How can you highlight report records that include values greater than $500 in the orderamt field?

One of the best things about Access desktop databases is the ability to store large amounts of data and keep it organized. But sometimes you wind up with a screen full of numbers or text and it can be hard to pick out what’s really important. That’s where conditional formatting can help. You can set rules for each field on a form, and automatically highlight values according to those rules.

Note:  Conditional formatting is not compatible with the Publish to Access Services feature in Access 2010, so the command is not available on the Format tab when you’re working on a web-compatible form. Conditional formatting is also not available with Access web apps. However, for standard client forms, conditional formatting can help data stand out according to rules you set.

1. In the Navigation Pane, right-click the form that you want to change, and then click Layout View.

2. Select the column or field to which you want to apply conditional formatting. To apply the same formatting rules to multiple columns or fields, hold down the CTRL key and click each one until they are all selected.

3. On the Format tab, click Conditional Formatting.

4. In the Conditional Formatting Rules Manager dialog box, click New Rule.

5. Enter the criteria for your rule.

   • To format the field(s) based on the values they contain, select Field Value Is from the first list, and then select or enter the criteria you want.

   • To format a field based on a calculation, select Expression Is from the first list, and then type the expression in the box. For more information about expressions, see the article Introduction to expressions.

   • To apply formatting to the selected field only when the field has the focus, select Field Has Focus from the first list.

6. Select the formatting that you want to apply. You can set the following styles:

   • Bold

   • Italic

   • Underline

   • Background color

   • Font color

   • Enable (this option disables the field so that it cannot be edited, and overrides the Background color and Font color settings).

   Access displays a preview of the formatting as you select options.

7. Click OK.

8. To add a new rule to the same field or fields, click New Rule and repeat this procedure from step 5.

   Note:  You can add up to 50 rules per field or group of fields. The rules are evaluated from top to bottom. Once the criteria for a rule have been met, Access applies the formatting for that rule and does not evaluate the rules below it.
   

9. When you are finished adding rules, click OK in the Conditional Formatting Rules Manager dialog box.

Top of Page

1. In the Navigation Pane, right-click the form that you want to change, and then click Layout View.

2. Select the column or field to which you want to apply conditional formatting. To apply the same formatting rules to multiple columns or fields, hold down the CTRL key and click each one until they are all selected.

3. On the Format tab, click Conditional Formatting.

4. In the Conditional Formatting Rules Manager dialog box, click New Rule.

5. Under Select a rule type, select Compare to other records.

6. Under Edit the rule description, select the options you want, and then click OK.

Top of Page

Conditional formatting allows you to apply different formatting to individual values on Access desktop reports, either based on the value itself, or on a calculation that includes other values. This can help you see patterns and relationships in the data that might be hard to spot otherwise. This video demonstrates adding some simple conditional formatting rules to highlight certain ranges of values on a report.

Note: Conditional formatting is not available in Access web apps.

1. Open the report in Layout view by right-clicking the report in the Navigation Pane and then clicking Layout view.

2. Select all of the controls to which you want to apply the conditional formatting. To select multiple controls, hold down the SHIFT or CTRL key and click the controls you want.

3. On the Format tab, in the Control Formatting group, click Conditional Formatting. Access opens the Conditional Formatting Rules Manager dialog box.
   

4. In the Conditional Formatting Rules Manager dialog box, click New Rule.

5. In the New Formatting Rule dialog box, select a value under Select a rule type:

   • To create a rule that is evaluated for each record individually, select Check values in the current record or use an expression.

   • To create a rule that compares records to each other by using data bars, click Compare to other records.

   Note:  The Compare to other records option is not available in Web databases or Access web apps.

6. Under Edit the rule description, specify the rule that will determine when the formatting should be applied, as well as the formatting that you want when the rule’s criteria are met.

7. Click OK to return to the Conditional Formatting Rules Manager dialog box.

8. To create an additional rule for this control or set of controls, repeat this procedure from step 4. Otherwise, click OK to close the dialog box.

You can add up to 50 conditional formatting rules for each control or group of controls. Once a rule’s criteria are met, the corresponding format is applied, and no more conditions below that one are evaluated. In the case of conflicting rules, you can increase or decrease the priority of a rule by moving it up or down in the list. Use the following procedure:

1. Open the Conditional Formatting Rules Manager dialog box by following steps 1 through 3 in the preceding procedure.

2. Select the rule that you want to move, and then use the up and down arrows to move it.