Take a look at the video tutorial below of the latest version ( Access 2013 ) which demonstrates three different methods between using the value, expression and an aggregate rule.
Access Conditional Formatting: How To Apply Logical Formatting To Access 2013 Reports And Forms
The pre-defined data bars when using the built-in ‘compare to other records‘ rule really makes this easy to apply and bring a dynamic impact typically to your reports.
You can set from a value range or percentage aggregate and choose from any defined colour required.
I like the way the video demonstrated how you can re-purpose a (duplicate) field to show for a different emphasis of the report without the need to add any extra calculated fields albeit in the report, form or from the base query.
Using the ‘expression‘ rule type really allows simple logic to be applied to the whole record which will be required should you want to show row formatting based on one or more field values.
Also note that you can use Access conditional formatting directly on calculated fields too which will need to be considered for the more complex returning value to a record that doesn’t really exist in any data table.
The scope for a calculated field needs to be considered too and the rule is simple; if a value is considered a one-off benefit, stick it into the form or report. If not, create it in the query which makes this potentially a re-usable field in other forms and reports.
Finally, to keep the number of query objects to a minimum to hide SQL statements (which is what queries really are) without hiding Access objects, embed the SQL code into the form or report ‘Record Source‘ property which makes this really a local recordset to the object.
Want to learn more about MS Access? Take a look at my eBooks.