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:
- Right-click the report tab and select Design View (or right-click in a blank area on the report and select Design View).
- Right-click the Detail section.
- 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:
- Right-click the report tab and select Layout View (or right-click in a blank area on the report and select Layout View).
- 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.
- Click the Report Design Tools Format tab in the Ribbon.
- Click Conditional Formatting in the Control Formatting group.
- In the dialog box that appears, click New Rule.
- Click Check values in the current record or use an expression or click Compare to other records.
- Enter the desired rule.
- Select the formatting you want to apply such as font color.
- Click OK twice.
- 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:
- Right-click the report tab and select Design View (or right-click in a blank area on the report and select Design View).
- 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.
- Click the Report Design Tools Format tab in the Ribbon.
- Click Conditional Formatting in the Control Formatting group.
- In the dialog box that appears, click New Rule.
- Click Check values in the current record or use an expression or click Compare to other records.
- Enter the desired rule.
- Select the formatting that you want such as font color.
- Click OK twice.
- 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)
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
How to Create a Calculated Field in a Microsoft Access Query
You can create calculated fields in select queries in Microsoft Access in the QBE (Query by Example) grid. You’ll need to learn a few syntax rules and then you can create simple to more complex calculations.
10 Useful Microsoft Access Shortcuts When You’re Working with Controls on Forms and Reports
Check out these great shortcuts for manipulating controls in Design View in both forms and reports in Microsoft Access.
How to Stop or Control Microsoft Access Error Checking Markers
In Microsoft Access, small green error markers may appear in Design View in forms and reports for a number of reasons. You can turn error checking off completely or set the rules you prefer.
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
