Use conditional formatting in Access forms and reports

Access' conditional formatting feature is just as comprehensive, flexible, and powerful as Excel's, but many users aren't aware it exists.

Many Access users would like to highlight report information, conditionally; similar to the way Excel users employ conditional formatting. For instance, the simple report shown below displays a due date in red when it falls within the current week. It's an easy way to visually alert users that a project enters its final week.

Fortunately, this report solution doesn't require a complex expression or even VBA - your users can use a conditional formatting rule. If your users are familiar with Excel, they'll need little training because the feature works similarly in both applications. Before we work through the example, you should know that I based this example on the Events template that comes with Access 2010. You can work with most any report, as long as there's a date field to work with. I entered just three records to keep things simple. To apply this particular conditional formatting rule to a date field in a report (the All Tasks report), do the following:

  1. Open the report in Design view.
  2. Select the date field. In this case, that's the Due Date field.
  3. Click the contextual Format tab.
  4. Click Conditional Formatting in the Control Formatting group.
  5. In the resulting dialog, click New Rule.
  6. The Select A Rule Type option will default to the right option, Check Values In The Current Record Or Use An Expression, so don't change that option.
  7. Now you're ready to define the rule. Field Value Is is the appropriate option in the first dropdown control, so don't change it.
  8. Choose Less Than Or Equal To from the second dropdown.
  9. Enter the following expression: Now()+7. (That period is grammatical and not part of the expression.)
  10. From the Font Color dropdown, choose Red.
  11. Click OK. Your report should resemble the one shown above in Report View.

In English, this rule says the following: If the due date falls within the next seven days, display that date in red.

In this example, we applied only one simple rule, but the feature is much more robust. Access' conditional formatting feature is just as comprehensive, flexible, and powerful as Excel's. Use it to alert users conditionally, in both forms and reports.