Display conditional data in an Access report

Use conditional formatting to create a truly comprehensive and helpful report in Access.

Turning data into information may be the most important task when creating a report, but a truly great report goes a step further. The most comprehensive report will provide everything the reader needs.

For instance, let's suppose you generate a sales report similar to the Top Ten report in Northwind (the sample database that comes with Access). As it, the report is good, but let's suppose you know that your manager calls customers with sales exceeding $10,000, just to say thank you for their business. Now, you know that your manager has the customer phone numbers somewhere, but having those phone numbers right at his/her fingertips while reading the report would be helpful, wouldn't it?

The first step to enhancing this report is to make sure the data (the customer phone number) is available to the report via the underlying data source. In the case of the Top Ten report, you'd add the phone field (Business Phone) to the Top Ten Orders By Sales Amount query. (Be sure to save and close the query when you're done.)

Next, add the Business Phone field to the Top Ten report. With the report in Design view, drag the field from the Field List. In Access 2003, click Field List on the Report Design toolbar. In later versions, click the Design (context sensitive) tab and then click Add Existing Fields in the Tools group. Next, simply drag the Business Phone field from the Field List to the report.

As is, the report will display the phone number for each customer in the report. Displaying unnecessary phone numbers--those for customers the managers won't call--presents additional data the reader must wade through to get what they really need. Fortunately, Access 2010's conditional formatting makes a chore like this easy.

Before you can apply conditional formatting though, you must rename the SaleAmount control. The conditional format will rely on an expression that references SaleAmount and Access can't evaluate expressions in forms and reports when the control name and the underlying field are the same. Access doesn't know whether the expression is referring to the control or the underlying field. This basic rule applies to all report (and form) expressions—anytime you add expressions to a report (or form); you must rename the controls referenced by the expression. In this case, that means you must rename SaleAmount to txtSaleAmount. (You can rename the control most anything you like, but adding the txt prefix is simple and follows standard naming convention guidelines.)

After renaming the control, apply the conditional formatting as follows:

  1. With the report in Design view, select the Business Phone control.
  2. On the Format (context) tab, click Conditional Formatting in the Control Formatting group. In Access 2003, choose Conditional Formatting from the Format menu.
  3. Click New Rule. (Skip this step in Access 2003.)
  4. The default Select A Rule Type is Check Values In The Current Record Or Use An Expression. That's what you want, so don't change that setting. (You won't see this option in Access 2003.)
  5. From the Format Only Cells Where The dropdown choose Expression Is . (In Access 2003, click the Condition 1 dropdown).
  6. Enter the condition expression [txtSaleAmount]>=10000.
  7. Now, specify the format. Click the Font Color dropdown (under the expression control) to display the palette. Choose Red and click OK.
  8. Repeat steps 3 through 5.
  9. Enter the expression [txtSaleAmount]<10000. Click the Font Color dropdown, choose White, and click OK.|
  10. Click OK again. The conditional formats display phone numbers in red for customers with high sales. The report doesn't display phone numbers for the other customers.

The best reports are more than just formatted data.  Display data under certain conditions is an easy enough technique--thinking to employ it might be the real challenge.