Conditional Formatting Introduction

Conditional Formatting Introduction. I apologise to all Microsoft Access 97 users as Conditional Formatting was not introduced to Microsoft Access until the 2000 version.

First of all, what is conditional formatting.

The most common use for this is on continuous forms. Have you ever tried to set up formatting of a text box, or similar, in a continuous form, depending on the value in that text box? It seems like a simple thing to do, just like it is on a single form, but it isn’t.

The formatting of the text box changes depending on the value of the current record of the form, the problem is the text box formatting changes for all records and this is often not what you want (although it maybe). You want each individual text box to reflect the formatting for the current value of that text box, whether it is the current record or not.

One of the simplest uses of conditional formatting is when dealing with currency (money). You can set the conditional formatting to have black text for positive numbers and red text for negative numbers, much the same as how you can format Excel.

We will be looking at how to set this up to give you a feel of what conditional formatting does,

To set up conditional formatting:

If you can, then use a form that is of a continuous format. This can be a stand-alone form or a continuous subform on another form. If you do not have one, then just make a form based on a table, choosing continuous form when you get to that option. If you can choose a table that has a field of a currency data type, that would be good. If you do not have such a field, add one to a table you already have. Add some currency values to this field, making sure you have both positive and negative values.

Once you have your continuous form and corresponding currency data organised, it is time to set up the conditional formatting. To do this:

Open the continuous form in Design view;

Click on the currency field to select it;

Then, on the menu bar, select ‘Format’, then ‘Conditional Formatting’;

A window will open. The top part will display the format of the text box if none of the conditions we are going to set is met. (In our example, this is not likely to happen as we are going to cover all possible scenarios).

The second section allows us to enter our first condition:

We are going to set the format to be RED, if the currency value is negative.

Therefore, leave the first combo box as is, ‘Field Value is’;

Change the second combo box to ‘Less Than’;

Finally, put 0 in the third section.

Now it is time to set the format. Click on the ‘Red’ A to change the font to RED. Note the text in the display section turns to red.

Click on ADD.

A new section for another condition appears. This time we are going to change the font to black if the currency value is 0 or positive.

Therefore, leave the first combo box as is, ‘Field Value is’;

Change the second combo box to ‘Greater Than or Equal To’;

Finally, put 0 in the third section.

Now it is time to set the format. We want a black font, so there is no need to change anything. Click on ‘OK’ to accept.

Notice you can also change the background colour, and whether the font is bold or italic, etc.

When you are all done, open the form in Normal view to check your results.

All working? That concludes this lesson on Conditional Formatting Introduction.

Well Done.

Do you like this tip? Subscribe to my Newsletter to receive tips via email. Click Here to subscribe.