Using Conditional Formatting in Access 2010 Reports

  • Article

Office Visual How To

Summary:  Learn how to use Microsoft Access 2010 to add support for simple conditional formatting in reports, which allows for multiple conditions and formatting options.

Applies to: Access 2010 | Access Services | Office 2010

Published:  December 2010

Provided by:  Ken Getz, MCW Technologies, LLC

Overview

This article describes how to use the new conditional formatting in Access 2010 reports. Conditional formatting enables you to select formatting for values based on one or more conditions at run time. Conditional formatting also allows you to create data bars, which enables you to view and compare values in multiple rows.

Code It

Starting in Access 2010, you can now use conditional formatting to bring attention to specific report details. For example, you can visually highlight values that identify departments that are performing especially well, or highlight departments that are under-performing. In addition, you can display data bars for reports that are based on named tables or queries. By using data bars, you can visually compare values across all data rows.

Creating the Sample Database

The first step in demonstrating the new formatting features is to create the sample database. Once you have created the database, you can apply the formatting features to a simple report.

To create the sample database

  1. Start Access 2010.

  2. In Access, click File and then click New.

  3. In the list of available templates, under Office.com templates, select Samples.

  4. From the list of sample databases, select Northwind 2007.

  5. Select an appropriate path, and then click Download to download and install a clean copy of the sample database.

  6. If asked, click Enable Content in the Message Bar to enable the VBA code in the sample database.

  7. Accept the default login ID in the Login Dialog form, and click Login.

    At this point, you should have downloaded and installed a fresh copy of the Northwind 2007 sample database. In addition, the database should be loaded in Access 2010.

  8. Close the default Home form.

Preparing to Add Conditional Formatting

In this example, you will highlight values in the report named Quarterly Sales Report. The Quarterly Sales Report displays sales for each of the three months in a quarter, together with a total for the quarter. You will configure the formatting of the report to point out both high and low sales values. Specifically, you will display monthly sales totals less than $500 in bold red. You will also configure the report to display the monthly sales totals larger than $3000 in bold green. Finally, you will format $0 values to appear in gray.

To prepare to add conditional formatting

  1. In the list of available reports, find the report named Quarterly Sales Report, and open the report in Layout View.

  2. In any row of data, select the first column of numeric data, hold down Shift and click the right mouse button on the third column, selecting the first three cells in the row (see Figure 1).

    Because the report is a repetition of the same data for each row in the underlying table, you only have to select a single row to affect your changes for all rows in the report.

    Figure 1. Select a row of data


    Row of data

  3. In the ribbon, select the Format tab, and in the Control Formatting group, click Conditional Formatting.

    This action displays the Conditional Formatting Rules Manager dialog box.

Examining the Conditional Formatting Rules Manager Dialog Box

Once you have displayed the Conditional Formatting Rules Manager dialog box, you can use the features present to modify the way your report displays text.

To examine the Conditional Formatting Rules Manager dialog box

  1. In the Conditional Formatting Rules Manager dialog box, notice the Show formatting rules for drop-down list (see Figure 2).

    This list lets you select a single control to format, or, as in this case, displays Multiple when you have selected multiple controls.

    Figure 2. Select the controls to format


    Show formatting rules dropdown list

  2. Click New Rule.

    Clicking New Rule displays the New Formatting Rule dialog box, as shown in Figure 3.

    Figure 3. The New Formatting Rule dialog box


    New Formatting Rule dialog

  3. Examine the Select a rule type list, which lets you select which kind of rule that you create.

    By default, you create a rule that checks values in the current record (or uses an expression). You can also select the Compare to other records option. This option allows you create data bars so that you can visually compare data in multiple rows (see Figure 4).

    Figure 4. Compare to other records


    Compare to other records value

  4. In the Select a rule type list, select Check values in the current record or use an expression.

  5. In the options for the rule, select Field Value Is, equal to, and 0.

  6. From the formatting options, select gray as the foreground color, as shown in Figure 5.

    Figure 5. Set up formatting for $0 values


    Grey foreground color

  7. Click OK to add the new rule.

    The dialog box updates to display the new rule, as shown in Figure 6.

    Figure 6. Dialog box displays the new rule


    Updated dialog box

  8. Now, select the New Rule, Edit Rule (which displays the dialog box you previously used), or Delete Rule button.

  9. At this point, click New Rule to create a second rule.

  10. In the options, select Field Value Is, less than or equal to, and 500.

  11. Set the formatting so that matching values appear in bold red (see Figure 7).

    Figure 7. Create a new rule


    Values appearing in bold red

  12. Click OK to add the new rule.

  13. Create a new rule, this time selecting Field Value Is, greater than or equal to, and 3000.

  14. Select bold and green foreground formatting, and then click OK.

    When you have finished, the set of rules should resemble Figure 8.

    Figure 8. Complete set of rules


    Set of rules with formatting

  15. Click OK, dismissing the dialog box, and verify that the report now appears with various values formatted appropriately, as shown in Figure 9.

    Figure 9. Formatted report


    Report with formatting

    Important

    Ordering rules is significant: when Access 2010 finds a rule that matches a value, the application stops looking for additional rules that might apply to the value.

  16. To demonstrate the significance of ordering, again display the Conditional Formatting Rules Manager dialog box, select the middle rule, and then click the up-arrow button (see Figure 10).

    This action adds the rule that checks for values less than or equal to 500 above the rule that checks for values equal to 0.

    Figure 10. Rearrange the rules


    Dialog box with additional rule

  17. Click Apply.

    All the 0 values change to bold red formatting; Access never reaches the middle rule that compares to 0.

  18. Select the top rule, and then click the down-arrow button to put the rule back in the middle.

  19. Click OK to accept the changes and dismiss the dialog box.

    Notice that the formatting reverts to its original appearance.

Creating a New Report

You can also apply other kinds of conditional formatting, such as comparing values in all the rows. To do so, you must start with a report whose data is based on a named table or query.

To create a new report

  1. Close any open windows in the Access designer.

    The Northwind sample database does not include an appropriate report; therefore, you need to create a new report.

  2. In the Database Navigation pane, expand the list of queries, and then select the Purchase Summary query.

  3. Select the Create tab, and in the Reports group, select Report.

    This action creates a new report that is based on the Purchase Summary query and opens the new report in Layout View.

Adding Data Bars

You may find it useful to visually compare values in multiple rows of a report. The simplest way to visualize numeric values is by using data bars.

To add data bars

  1. In the new report, select a value in the Order Total column.

  2. In the ribbon, select the Format tab, and in the Control Formatting group, click Conditional Formatting.

  3. In the Conditional Formatting Rules Manager dialog box, notice that only the Order Total field is selected in the list of fields.

  4. Click New Rule, and in the New Formatting Rule dialog box, select Compare to other records.

  5. Accept the default options (Lowest value and Highest value) for the Shortest Bar and Longest Bar options, and change the bar color to red.

  6. By default, the report displays the value together with the bar. You can remove the display of the value by selecting the Show Bar Only option. Leave this option cleared.

  7. Click OK, and then OK again, and your report should now display the Order Total field as a bar, as shown in Figure 11.

    Figure 11. Add data bars


    Report with Order total field as a bar

Read It

Be careful when you create multiple conditional formatting rules. As stated previously, Access stops looking at the rules as soon as Access finds one that matches a value. If you have multiple rules, but you are not getting the formatting you expect, check the logic of overlapping regions of data. You may have reversed the logic specifying the formatting for a particular value.

The length of the bars is comparative. Therefore, when you are creating data bars, be sure to select the meaning of the shortest and longest bars. Normally, those values will be the smallest and largest data points. However, you have other options: in the list of options for Shortest Bar and Longest Bar, you can also select Number and Percent. Selecting Number enables you to enter a specific number that represents the value of the shortest (or longest) bar. All values less than or equal to the shortest value appear with length 0. In contrast, all values greater than or equal to the longest value appear as full-length bars. Selecting Percent enables you to specify a percentage of the lowest (or highest) value to act as the limit on the shortest (or longest) bar. For example, setting the shortest bar value to 30% causes any value less than or equal to 30% of the largest value to appear as a zero-length bar.

 See It

Watch the video

> [!VIDEO https://www.microsoft.com/en-us/videoplayer/embed/05644f1b-256a-430b-b957-8bfdc4234fa0]

Length: 20:29

Explore It

About the Author
Ken Getz is a senior consultant with MCW Technologies. He is coauthor of ASP.NET Developers Jumpstart (Addison-Wesley, 2002), Access Developer's Handbook (Sybex, 2001), and VBA Developer's Handbook, 2nd Edition (Sybex, 2001).