Tip:
Highlight text to annotate it
X
Hi, I’m Chris Downs, a writer for Microsoft Access.
One of the best things about databases is the ability to
store large amounts of data and keep it organized.
But sometimes you wind up with a screen full of numbers or text
and it can be hard to pick out what’s really important.
That’s where conditional formatting can help.
You can set rules for each field on a form,
and automatically highlight values according to those rules.
Note that conditional formatting is not compatible with
the Publish to Access Services feature in Access 2010.
So, the command is not available when you’re working on a
Web-compatible form.
However, for standard client forms, conditional formatting can
help data stand out according to rules you set.
This form shows me inventory levels and how far below the target level
of inventory each one is.
I want to highlight the ones that are really far below target level,
so I can prioritize which ones to reorder first.
For example, I might want to have Access highlight the values
that are 50 or more units below target level.
I’ll switch to layout view by right-clicking the document tab
and selecting the view.
I’ll select the Below Target Level column, and on the Format tab,
I’ll click Conditional Formatting.
I click New Rule, and then create a rule that says
when the field value is greater than or equal to 50,
To format the text in bold, with a red background and white foreground.
And here’s a preview of how it will look.
I can add up to 50 conditional formatting rules
for each control or group of controls.
For example, I could establish different formats for different
ranges of numbers.
I click OK on the dialog boxes, and switch back to Form view.
So now, any products that are 50 or more units below target
are easy to see.
Since I have different target levels for different products,
it might be more meaningful to base my formatting on a percentage.
For example, I might want to highlight only the records
that are below the target level by 20% or more.
So, I’ll switch back to Layout view, select the field,
and click Conditional Formatting again.
I’ll select the rule and then click Edit Rule.
To calculate a percentage, I’ll need to use an expression.
I select "Expression Is" from the first list,
And then I enter the expression in the box:
BelowTargetLevel divided by TargetLevel
is greater than or equal to 20 percent.
I’ll click OK and switch back to Form view.
Now a few more values are highlighted,
since I’m not just cutting off at an arbitrary number.
Conditional formatting can also be helpful on a single-item form.
As I click Next Record in the status bar,
the conditional formatting alerts me to products I need to reorder.
Another use for conditional formatting is
to compare values across records.
For example, in this list of invoices,
I want to see at a glance which invoices are bigger than average, and which are
smaller than average.
For this, I can use data bars.
I switch to Layout View and select the column that contains
the data that I want to format.
On the Format tab, I click Conditional Formatting,
and then I click New Rule.
Under Select a rule type, I click Compare to other records.
I’ll choose a little bit lighter color, and click OK.
Now, when I switch back to Form View,
the larger invoices are much more noticeable.
You may not have known that conditional formatting can also be used to
control access to data.
On this customer detail form, I’ve decided that I don’t want users
to be able to modify address information for any customers
who are listed as the owners of their companies.
I’ll switch to Layout view.
I’ll select the first address field that I want
this rule to apply to, and then I’ll hold down the SHIFT key
and click the last address field.
Access selects all the fields in between.
On the Format tab, I click Conditional Formatting, and New Rule.
Since the rule will span several fields, I need to use an expression.
In this case, I want the rule to take effect
when the JobTitle field contains the string "Owner",
so I’ll just type that in, with the word "Owner" in double-quotes.
Instead of formatting the text by using these format buttons,
I’m going to click this little Enable button.
Doing this will disable the Address text boxes
whenever the Job Title field contains the word "Owner."
I’ll click OK on the dialog boxes, and switch back to Form view.
Now, as I click the Next Record button,
you can see that the address fields are grayed out
for any customer who is listed as the owner of their company.