databasedev.co.uk - database solutions and downloads for microsoft access

Create Better MS Access Reports
Report Builder for Microsoft Access helps you create dynamic, professional-looking reports fast! The easy-to-understand wizard helps you with complex tasks like calculated fields, adding subreports, customizing styles, as well as grouping and sorting. Download a free trial today!

Conditional Formatting

Conditionally Format Microsoft Access Report Rows:

Microsoft Access 2000 and above contains options to use conditional formatting in reports and forms.

If a control on a form or report contains values that you want to monitor, apply conditional formatting to the control to make it easier to spot. For example, you can set conditional formatting so that if an employee's salary exceeds £100,000 the field's background turns red. Or you can set formatting so that text in the City field is green and italic if a customer resides in London.

You may want to highlight the whole row in a Microsoft Access report if the data in that row meets a certain criteria as the example will show.

If we use a membership report as the example, we would like to format rows differently where the membership is not currently active.

The record contains a checkbox called ysnActiveMember (If checked the membership is currently active, if not the membership is inactive and would need renewing). The design of the report is shown below:

The report in design view, showing the detail section and the Active checkbox
The report in design view, showing the detail section and the Active checkbox

To conditionally format the controls in the Detail section of the report, we need to select all of these (excluding the Active checkbox) and then open Format » Conditional Formatting.

We will set the Condition to - Expression Is: [ysnActiveMember]=False

This will allow us to conditionally format any records in the detail section where the Active field is unchecked (or the membership needs to be renewed). We can then set formats to show the records - I have set the controls to: Text Colour = Red, Weight = Bold as shown in the example below:

Showing the formatting set up in the Conditional Formatting dialog
Showing the formatting set up in the Conditional Formatting dialog

Now when we run the report, it is easy to pick out any records that have met the conditions that you have applied:

The finished report showing the conditionally formatted records
The finished report showing the conditionally formatted records

Using conditional formatting in Microsoft Access 2000, XP or 2003 allows you to set multiple conditions in your database forms and reports.