Take a look at the picture of the coffee beans below. If you look closely, you will see a face. It’s not easy to see because it’s mixed in with all of the other coffee beans in the picture. If you find this type of task frustrating, scroll down to the very bottom of this
Wizard for a picture of the face highlighted.
Think about information in a form or report. If there are one or several data points that are especially important, you want make it easy for the user find. You want it to jump out rather than having to search for it like the hunt for the face in the jumble of beans above. Within Access, this is what Conditional Formatting can do.
The Task and the Difficult-to-Find Conditional Formatting Tool
Look at the form below. If you are interested in the best and worst months’ results, you have to do some hunting to determine which ones are good and bad months.
Now look at this form. Your eyes are drawn to the important information with the highlights colors amid the sea of black numbers.
To create this type of effect, Access has a powerful tool called Conditional Formatting. It is found in the form design view: Click on the Form Design Tools tab, then the Format tab. I guess that is the logical spot for that control tool to live, but my experience is that it is difficult to find; rather ironic given that its purpose in the world is to make things easier to find.
The Mechanics
When you click on the tool, you will see the initial formatting rules manager dialog box below. It will give you a gateway to apply special formatting to controls and values for which you have developed rules. Because I was on the amount field when I pushed the button, that field became the default control for the special formatting. The target control is easy to change by adjusting the “show formatting rules for” drop-down.
When you click the new rule button, you will see a dialog box similar to the one below. I have filled in “field value is”, “less than”, and “5000” as rule for the values to format. When the value meets that criterion, I want the number to change to red, along with the bold font face. Note that the preview line gives you some idea of what things will look like when a value meets the conditional formatting rule.
Once that step was complete, I then clicked the new rule button again and added a second rule for when the value is greater than 10,000. In that case, the numbers will be green. These 2 rules in tandem will be applied to my amount field.
After clicking the Apply button and then running the form, you can see that the values below 5000 and above 10,000 get the conditional format.
Conclusion
This month, I showed you how to make values in a form standout by applying conditional formatting rules. This tool is available for reports as well as forms. I urge you to use this tool any time you would like to make it easier for you or your users to tune in to important information.
Know that that you are able to make the rules complicated if that’s what you need. The formatting can depend not only on the value in the formatted control, but also can be applied depending upon the values of other controls on the same record, as well as when comparing an existing record to other records.
It is a powerful tool that will make your forms and reports more effective.
