Apply Conditional Formatting in Microsoft Access Reports

by Avantix Learning Team | Updated October 14, 2022

Applies to: Microsoft® Access® 2010, 2013, 2016, 2019, 2021 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:

Conditional formatting button in the Ribbon in a Microsoft Access report.

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

Do you want to learn more about Microsoft Access? Check out our virtual classroom or in-person classroom Access courses >

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.

Apply 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):

Conditional formatting dialog box in Microsoft Access with rule set for numbers.

Apply 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):

Conditional formatting dialog box in Microsoft Access with rule set for dates.

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.

More resources

Key Features and Benefits of Microsoft Access

How to Filter a Report on the Fly in Microsoft Access

10 Tips for Creating Select Queries in Microsoft Access

10 Techniques for Designing Forms in Microsoft Access

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

Related training

Microsoft Access: Introduction

Microsoft Access: Intermediate / Advanced

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

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2024 Avantix® Learning

You may also like

How to Replace Zeros (0) with Blanks in Excel

How to Replace Zeros (0) with Blanks in Excel

There are several strategies to replace zero values (0) with blanks in Excel. If you want to replace zero values in cells with blanks, you can use the Replace command or write a formula to return blanks. However, if you simply want to display blanks instead of zeros, you have two formatting options – create a custom number format or a conditional format.

What is Power Query in Excel?

What is Power Query in Excel?

Power Query in Excel is a powerful data transformation tool that allows you to import data from many different sources and then extract, clean, and transform the data. You will then be able to load the data into Excel or Power BI and perform further data analysis. With Power Query (also known as Get & Transform), you can set up a query once and then refresh it when new data is added. Power Query can import and clean millions of rows of data.

How to Freeze Rows in Excel (One or Multiple Rows)

How to Freeze Rows in Excel (One or Multiple Rows)

You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you freeze rows containing headings, the headings will appear when you scroll down. You can freeze columns as well so when you scroll to the right columns will be frozen.

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 |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca

Summary
How to Highlight Records or Values in a Microsoft Access Report Using Conditional Formatting
Article Name
How to Highlight Records or Values in a Microsoft Access Report Using Conditional Formatting
Description
You can highlight values in fields or records in Microsoft Access reports using conditional formatting. 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.
Author
Publisher Name
Avantix Learning Inc.

Pin It on Pinterest

Share This