Yes—Microsoft Access can apply conditional formatting to fields in a query’s Datasheet View, as well as to form and report controls that display query results. In a query datasheet, select a field and choose Home > Text Formatting > Conditional Formatting; the rules change how values look, not the query’s data or results.
What conditional formatting means in Access
Conditional formatting tells Access to change a control’s appearance when a condition is true. Depending on the view and available formatting options, that can mean changing a text color, background color, or font style. For example, an order query could show overdue dates in red or negative balances with a colored background. The underlying date and balance remain unchanged.
It is a display feature, not a query calculation. Access still returns the same records, values, and field types whether a formatting rule is present or not. A user can still filter, sort, copy, or edit data according to the query’s permissions and design. Conditional formatting does not itself mark a record as overdue, prevent an edit, or save a status value.
This distinction matters because “formatting a query” can mean two things. You can format the query’s datasheet display with conditional rules, or you can create calculated fields and criteria in the query design. The first controls appearance; the second affects what the query calculates or returns.
Where Access supports it
In the Microsoft Access desktop application for Microsoft 365 on Windows, conditional formatting can be used in a query’s Datasheet View. It is also available for controls on forms and reports, including controls whose Control Source is a query field. The right place to create a rule depends on how people use the data.
- Query Datasheet View: Useful for a quick, grid-style review of query results. Select the field to format, then use Home > Text Formatting > Conditional Formatting.
- Form: Useful when users work with one record or a designed entry screen. Open the form in Layout View or Design View, select the relevant control, and use Format > Control Formatting > Conditional Formatting.
- Report: Useful when the result is presented or printed as a report. Select the report control in Layout View or Design View and use Format > Control Formatting > Conditional Formatting.
Access is a Windows desktop application; there is no native Microsoft Access desktop version for macOS. A Mac user generally needs access to Windows—for example, through a supported remote desktop or virtualized Windows setup—to use these Access ribbon commands. The paths above refer to the Windows desktop interface, not Excel or a browser-based spreadsheet.
How to add a rule to a query datasheet
First open the saved query in Datasheet View. If it is open in Design View, switch to Datasheet View using the View control on the ribbon or the query tab’s context menu. Select the column whose displayed values should change. The rule formats that selected field, even when the test refers to another field in the same record.
- Choose Home > Text Formatting > Conditional Formatting.
- In the Conditional Formatting Rules Manager, choose New Rule.
- Select a rule type. Use a field-value comparison for a straightforward test, or an expression rule when you need a logical expression or a comparison with another field.
- Set the condition and choose the formatting to apply when it is true. For example, choose a red font or a contrasting background for overdue items.
- Confirm the rule, close the dialog, and inspect several records, including records that should not meet the condition.
- Save the query if you want its datasheet formatting to be retained when the query is reopened.
For a form or report, the overall process is similar, but select the text box, label, or other relevant control rather than a datasheet column. The control must display the value or context that the rule is meant to evaluate. If a form control is bound to a query field, the form’s rule is attached to that control; it is not automatically the same rule as the one saved with the query datasheet.
Using expressions for more useful rules
Field-value rules are convenient when the condition is a simple comparison, such as a date being before today or a number being less than zero. Expression rules are more flexible. They let you test multiple conditions or compare one field with another. Access expressions use Access names and syntax; they are not Excel formulas, and they do not begin with an equals sign in the way a spreadsheet formula does.
For example, to highlight the Balance field when it is negative, an expression rule can test [Balance]<0. To highlight a DueDate field when the date is earlier than today, a rule can use [DueDate]<Date(). To format an Actual field when it exceeds a Limit field in the same record, use [Actual]>[Limit]. In each case, create the rule on the field or control whose appearance should change, and confirm that the names refer to fields available in the query or control’s record source.
A query can also expose a calculated field with an alias, such as a calculated number of days late. When a conditional-format expression refers to that result, use the field name exposed by the query and check the query in Datasheet View to confirm the expression is resolving as intended. Names with spaces or punctuation should be enclosed in square brackets, as in [Order Status].
Null values deserve special attention. A comparison with Null does not behave like a comparison with an ordinary number or text value, so a rule intended for missing data should explicitly test whether the field is null. For example, an expression may use [ShipDate] Is Null to identify records without a ship date. If an expression combines several conditions, test each part on representative records before relying on the visual result.
Choosing between a query, form, and report rule
Use a query datasheet rule when the query itself is the main way people inspect the results and a compact, grid-based display is sufficient. It is convenient for analysts or database users who open the query directly. However, it is tied to that query’s datasheet presentation and may not provide the clarity or control needed for a polished workflow.
Use a form rule when the data is presented through a user interface. A form can show labels, instructions, buttons, and related fields alongside the formatted value. This makes it easier to explain what a color means and to keep the display consistent for users who do not open queries directly. Use a report rule when the formatted result is intended for a printable or shareable layout. Check the report in Print Preview, because screen colors and printed output can differ by printer settings and color availability.
One practical example is a service database with a query that returns open tickets. A datasheet rule can highlight the Priority field when it says “Urgent.” A form can instead emphasize the ticket’s due date when it is past due, while still showing the customer and assigned employee. A report can use a similar visual cue in a weekly summary. Decide where the user needs to see the warning, then create the rule in that view rather than assuming a query’s formatting will carry through everywhere.
What conditional formatting cannot do
Conditional formatting cannot change the query’s criteria or update a field. A red date is still just a date; if a workflow requires an “Overdue” status to be stored, reported, or used by other processes, create appropriate data or query logic rather than relying only on color. Likewise, formatting does not enforce validation, permissions, or business rules.
Do not assume a datasheet’s visual styling will travel with the data. Exporting or copying query results to another application may retain values but not Access-specific conditional formatting. A form or report may also have its own formatting rules, separate from those on the query. If another person must understand the meaning after an export or printout, include a text status or an explanatory legend rather than using color alone.
Color should be a cue, not the only way to communicate important information. Red and green can be difficult for some people to distinguish, and a printed page may be grayscale. Pair color with a clear status word, icon, or other readable label where the design allows. Use sufficient contrast so text remains legible against the chosen background.
Common problems and how to diagnose them
If the rule seems to have no effect, begin with the target. Confirm that you selected the intended field or control and that the rule is attached to that object. Then confirm that the query actually returns the field named in the expression. A renamed output column or an alias that differs from the source table’s field name can make a rule test the wrong name or fail to match the expected value.
- Check the view: Query datasheet rules belong to the datasheet presentation. A form opened from that query does not necessarily inherit them.
- Check the condition: Test a simple case first, then add more logic. Confirm that the comparison operator and text value are correct.
- Check data types: Dates, numbers, and text are not interchangeable. A date stored as text may not compare as expected.
- Check Nulls: Blank or missing values may need an explicit Is Null test rather than an ordinary equality or inequality check.
- Check rule interactions: If multiple rules apply to the same control, review their order and formatting choices in the Conditional Formatting Rules Manager. Rules that set the same property can make the result confusing.
- Save and reopen: Save the query or the form/report design, close it, and reopen it to see whether the formatting persists in the intended object.
When a condition is based on another field, verify that the comparison is within the same record and that both fields are present in the query’s output or available to the control. Also inspect records with boundary values: a date equal to today, a balance of zero, or an amount exactly equal to its limit. Those cases reveal whether the operator should be less than, less than or equal to, greater than, or greater than or equal to.
Frequently Asked Questions
Can I apply conditional formatting directly to an Access query?
Yes. Open the query in Datasheet View, select the field, and choose Home > Text Formatting > Conditional Formatting. The rule affects the query datasheet’s appearance, not the records returned by the query.
Can an Access query conditional format compare two fields?
Yes. Create an expression rule on the field you want to format and compare it with the other field, such as [Actual]>[Limit]. Confirm that both names are available in the query’s output and that the rule is attached to the intended column.
Does query conditional formatting appear on a form or report?
Do not assume that it does. A form or report has its own controls and formatting rules. Apply a rule to the relevant form or report control if that is where users need to see the condition.
Can conditional formatting change the data in Access?
No. It changes the display only. To store a status, filter records, or prevent an invalid entry, use suitable fields, query logic, validation, or other database design features.
Why does my conditional formatting rule not work with blank values?
Blank database values are often Null, which does not compare like ordinary text or numbers. Use an explicit Is Null or Is Not Null test where appropriate, and check the field’s data type and the records returned by the query.
Related
- Find and Replace Font in Word: Full Steps and Tips
- Find and Replace Highlight Color in Microsoft Word
- Find and Replace in Word: Open and Use the Dialog Box
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Create a Public Distribution List in Outlook: StepsTo create a public distribution list for your organization, ask an Exchange or Microsoft 365 administrator to create…
- Excel Protected Sheet: Why You Can’t Delete Rows in ExcelIf you can’t delete rows in an Excel worksheet, the sheet is probably protected and its protection settings…
- Excel Column Selection: Select One or More ColumnsIn Excel for Microsoft 365 on Windows, click a column letter to select that entire column, or click…
- Excel Freeze Panes: Freeze More Than Just the Top RowNo—Excel’s Freeze Panes command is not limited to the top row. In Excel for Microsoft 365 on Windows,…
- Google Sheets FILTER Formula: How Do You Use It?The Google Sheets FILTER formula returns only the rows or columns that meet conditions you specify. For example,…
- How to Unblock a Sender in Outlook on Desktop and WebTo unblock a sender in Outlook, remove their email address or domain from your blocked-senders list, then check…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.