MS Access - Formatting

One especially useful formatting tool in Access is the ability to apply Conditional Formatting to highlight specific data. Let us take a simple example of conditional formatting.

Example

In this example, we will be using a form fSubCurrentProjects in our database.

We have a list of all of the projects in this database and we have also got a couple of new fields like the On Time Status and the Number of Late Tasks. This form is created from another query.

In this query, we have a join between a table and a query that will display the count of due dates or how many projects have tasks that are overdue. We also have a calculated field here that uses the IF function to determine whether or not the count of the due date is greater than zero. It will then display the words Late if the project is late or On Time if that specific project does not have any overdue tasks.

Example 1

In this example, we will be using the above form to understand how you can use Conditional Formatting to highlight specific pieces of information. We will now highlight every single project that is currently running late. To apply Conditional Formatting to one field or more than one field, we will need to switch over to the Layout view.

Now, select the On Time Status field.

Now, go to the Format tab.

On that Format tab, you should see a group called Control Formatting and a special button for Conditional Formatting. Let us now click on Conditional Formatting.

You will now see a Conditional Formatting Rules Manager and currently we have no rules applied to this control. Let us now create a new rule by clicking on the New Rule button.

You will now see a New Formatting Rule dialog box. We will first specify the type of rule we will be creating and here we have two options. The first option is to check the values in the current record or to use an expression, and the second option is to compare this record with the other records.

We now have only one of two values in our form; either On Time or the word Late and that is from the given query. Let us now select the “Field Value Is” from the first combo box and then select “equal to” from the second combo box. Now, type the word “Late” in quotation marks.

We can now set our Conditional Formatting, how we want this field to look like if the word Late appears in that field. Let us now change the font color to red and make it bold, italic and underline, and that's our conditional rule. Let us now click Ok and then click Apply, and Ok again.

You can see that the word Late is formatted now. This is one example of how to create a very basic conditional format rule.

Example 2

Let us take another example. Here, we will make the title or the name of the project red and bold, italic and underline. Select the project name control on your form.

We will now go back to our Format tab and click on Conditional Formatting and create a new rule for that specific control as shown in the above screenshot.

Here, we will not be checking the value of the current field we have selected, but we will be checking it against another field on this form. Select Expression Is in the first combo box and then click on … button at the end as in the above screenshot.

In the Expression Categories, you have every single object that is on this form. Doubleclick on CountofDueDate. This will send the reference to that control or that field up to our expression builder and condition if it is greater than zero. Now, click Ok.

Let us now click Ok and then, click Apply and Ok again.

That was an example of Conditional Formatting on a field based on values in another field.

Example 3

Let us now look at another example of conditional formatting. Let us assume, we want to see which projects are more late or have more late tasks than other late projects. Select the Conditional Formatting option.

Click on the New Rule button to create a new rule and then click Ok as in the above screenshot.

In the New Formatting Rule, we will now select a rule type “Compare to other records”. Let us further change the Bar color to red. We want our shortest bar to represent the lowest value and the longest bar to represent the highest value. Let us now click Ok and then, click Apply and Ok again.

You can now see Conditional Shading applied as in the above screenshot. Let us now go to the Form view.