How to Highlight Records or Values in a Microsoft Access Report Using Conditional Formatting

Apply Conditional Formatting in Microsoft Access Reports

by Avantix Learning Team | Updated August 1, 2020

Applies to: Microsoft^® Access^® 2010, 2013, 2016, 2019 and 365 (Windows)

You can highlight values or records in a Microsoft Access report using conditional formatting. Conditional formatting is used to change the appearance of a bound control such as a text box based on a rule. Certain conditions must be met in order for the formatting to be applied. You can apply conditional formatting in Layout View or Design View. Conditional Formatting appears in the Report Design Tools Format tab in the Ribbon:

Recommended article: How to Create a Calculated Field in a Microsoft Access Query

If you want to apply conditional formatting to controls, you may want to remove alternate row shading in Design View:

1. Right-click the report tab and select Design View (or right-click in a blank area on the report and select Design View).
2. Right-click the Detail section.
3. In the drop-down menu, select Alternate Row Shading and then select No color or white.

Applying conditional formatting in Layout View

To apply conditional formatting in Layout View:

1. Right-click the report tab and select Layout View (or right-click in a blank area on the report and select Layout View).
2. Click the control or controls to which you want to apply conditional formatting. If you want formatting to apply to the entire record, Shift-click on all of the controls in a single record.
3. Click the Report Design Tools Format tab in the Ribbon.
4. Click Conditional Formatting in the Control Formatting group.
5. In the dialog box that appears, click New Rule.
6. Click Check values in the current record or use an expression or click Compare to other records.
7. Enter the desired rule.
8. Select the formatting you want to apply such as font color.
9. Click OK twice.
10. Right-click the report tab and select Print Preview to see how the formatting will appear if you print the report.

In the example below, we’ve entered a rule so that if the actual sales is greater than 50000, the entire record will appear in bold and a blue font color (note that the field actualsales must bound to a control such as a text box on the report):

Applying conditional formatting in Design View

To apply conditional formatting in Design View:

1. Right-click the report tab and select Design View (or right-click in a blank area on the report and select Design View).
2. Select the control or controls to which you want to apply conditional formatting. If you want formatting to apply to an entire record, Shift-click on all of the controls in the Detail section.
3. Click the Report Design Tools Format tab in the Ribbon.
4. Click Conditional Formatting in the Control Formatting group.
5. In the dialog box that appears, click New Rule.
6. Click Check values in the current record or use an expression or click Compare to other records.
7. Enter the desired rule.
8. Select the formatting that you want such as font color.
9. Click OK twice.
10. Right-click the report tab and select Print Preview to see how the formatting will appear if you print the report.

In the example below, we’ve entered a rule so that if the followup date is within 90 days after the current date, the entire record will appear in bold and a green font color (note that the field followupdate must bound to a control such as a text box on the report):

If you want to apply fill formatting to an entire record, the controls (usually text boxes) would need to “touch” the next control or you would see gaps in the record.

Also, in the list of conditional formatting rules in the Conditional Formatting dialog box, a rule higher in the list takes precedence over a rule lower in the list. So if a value or record is formatted using conditional formatting using multiple rules, the rule highest in the dialog box would “win”. You can change the order of precedence by clicking the move up and move down arrows in the dialog box.

This article was first published on May 17, 2019 and has been updated for clarity and content.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, join our email list.

Related

How to Convert a Microsoft Access Report to PDF (3 Ways)

How to Filter a Report on the Fly in Microsoft Access

10 Techniques for Designing Forms in Microsoft Access

Recommended Microsoft Access training

Microsoft Access: Introduction

Microsoft Access: Intermediate / Advanced

Microsoft Access: Introduction to VBA (Visual Basic for Applications)

VIEW MORE COURSES >

Our instructor-led classroom training courses are delivered at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario. Contact us if you’d like to arrange custom training at your office on a date that’s convenient for you.

To request this page in an alternate format, contact us.

Copyright 2021 Avantix^® Learning

You may also like

MORE MICROSOFT ACCESS ARTICLES >

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca