By Tom Wickerath, Microsoft Office Access MVP
Add various colored text and other formatting to forms and reports in your database, depending upon which conditions are met.
Note: Techniques here show the Format property which is easier to set in vba code than conditional formatting
Microsoft Access 2000 and later versions provide conditional formatting for text boxes and combo boxes that allow you to change the display of the text from the regular settings applied in the Properties window, depending upon whether certain conditions you have identified have been met. This may be colored text, highlighted text, or bold, italic or underlined text. Conditional formatting can even allow you to enable or disable the text box or combo box control. However, this feature isn't available in earlier versions, and there are a few limitations in the built-in conditional formatting, as we'll soon find out.
In this example, we'll format a text field to display green text for positive numbers, red text enclosed by parentheses for negative numbers, replace 0 values with Zero, replace NULL values with Null, and display the words Zero and Null in black text.
Microsoft provides the Northwind database as a source for examples. Make a copy of this database and name it NWind.mdb, so that you can use it for this set of instructions without modifying the original database file.
We'll use the Discount field in the Order Details table, along with the Order Details Extended query and the Orders Subform in the Northwind sample database. (It will not matter whether we use a form or report for this exercise, so you may try this technique on a report later.)
In order to enter a negative value and nullify a record to demonstrate each desired format, we must modify the Order Details table. Open this table in design view and select the Discount field. Press <F6> to switch to the field properties in the lower window. Remove the existing validation rule from the Discount field. You must also set its required value to No in order to nullify a record. Next, open the table in datasheet view. Modify the Discount values for four of the records as indicated below, so that we will have a value greater than zero, 0, less than zero and NULL:
In order to restrict the records displayed in the subform opened by itself (not via a parent form) to just these four records, add a temporary criteria to the Order Details Extended query:
Then add the format for positive, negative, zero and NULL values to the Discount textbox of the Order Details subform:
Note: You can copy the following format and paste it into the Format property:
Notice the result when focus is set to the Product textbox (i.e., the blinking cursor next to "Queso Cabrales"). In all cases, the Extended Price textbox is not included in these images:
Notice the result when focus is set to the Discount textbox for the first record:
Notice the result when focus is set to the Discount textbox for the third record (i.e., the negative discount):
It appears as if a decimal places setting of 0 may be interferring with the intended format. Increasing this setting to 1 (Fig. 7), and then to 2 (Fig. 8), yields the following results with focus in the Product textbox:
Alternatively, you can set the decimal places to Auto, and this seems to work fine.
You can also use the built-in conditional formatting, which was introduced with Access 2000, but you are limited to three conditions. There isn't an easy way of including the parenthesis around negative values. The following works, but it also requires that the decimal places not be set to zero. The only advantage that I can see in going this route is a larger selection of colors and the ability to set additional formats, such as bold, italic, underline and fill/backcolor:
Fig. 10 shows the final result:
Created: Jan. 17, 2005
Last Updated: Apr. 24, 2008
Copyright © 2004 - 2008 Tom Wickerath All rights reserved. Reprinted at vb123.com.au by permission.
By Tom Wickerath who would like you to consider a small donation to his charity
Other Related Articles
Conditional Formats on Access Forms