The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Finding a Face in the Crowd — Making Important Information Stand Out with Conditional Formatting

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.



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.

Tip of the Month: Get a Quick Count of Records in a Table

If you need to get a quick count of the records in a table, you can use the function below:

DCount("*", "MyTable")

To apply this directly in a query, proceed the function with “=” (or with a “?” in the VBA immediate window).

One of the advantages of this approach is that it avoids opening the table. This can be especially helpful if you have a lot of tables where you need to get a count. You would do this by either creating an array of tables to inspect, or you could put the names of tables in another table in your database and have your VBA code retrieve the table names from that source table.

Be on the lookout for a future Wizard where I will show you powerful methods to get information on a lot of tables without doing a whole lot of work.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886