Microsoft Access Database: Discover How Easily to Apply Conditional Formatting in Microsoft Access

Microsoft Access Database: Discover How Easily to Apply Conditional Formatting in Microsoft Access

In the days before Microsoft Access 2000, trying to create dynamic format changes to a form or a report based on a changing value or an expression required either a macro or some VBA code which meant learning how to program your Access database application.

When version 2000 was released and up to and including version 2007, Conditional Formatting became a life saver as it provided a quick way to select up to three conditions which logically tested and changed the look of your formats without any programming skills at all.

In Access 2010, it has been improved and extends beyond the three conditions along with some other quick pre-defined options releasing the richness of Microsoft Access forms and reports that it brings to your application.

What’s Conditional Formatting?

Well, if you know Microsoft Excel, you probably know what this is but just in case you were out that day, here’s a preview.

Conditional Formatting is a format feature that is applied to your forms and reports to a field (control) that listens (looks) for the value to change which then applies a format of your choice based on the logical test it performs. The format options could be the font colour, bold, italic, background colours and other basic enhancements which is triggered from the TRUE value returned on the data value found.

For example, for a sales report showing monthly turnover for each product where the product monthly total falls below a target value; a format is set so that the value turns the number to red and bold which clearly stands out from the rest which did meet the target value (as a regular format).

The conditional test must be a logical statement that if TRUE will apply the format set or if found to be to FALSE is ignored. You can nest multiple conditions (up to 3 for versions pre 2010) which logically flows in the order set to trigger a conditional format (if it can). Therefore, you could have three different formats for the same field with different data value ranges.

How to set Conditional Formatting

There is little difference between a form and report when using this tool but here I’m going to focus on the report object in Microsoft Access.

In design view of a report, click on the field (control) which will contain the conditional format.

Right mouse click on the control and choose ‘Conditional Formatting’ from the pop-up menu or locate the same command from the toolbar or ribbon bar (depending on the version being used).

Decide if the rule is to be based on a value, an expression or if a form, Field has focus.

Set the logical operator or write the logical expression which can be a calculation (a formula).

Choose your format options which appear as icons and are self-explanatory using the preview to assist.

Click OK.

Repeat this if more than one condition is required to the same control or across other controls.

In Access 2010, you have an additional option to choose Compare to other records and you can also set data bars which are gradient filled bars using aggregate calculations and can specify data types and values which are also expressions. Use the Microsoft Access help for more information by typing the following keyword phrase “conditional formatting – compare to other records”.