Conditional Formatting In Forms And Reports

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, even without the conditional formatting feature provided by Microsoft Access 2000 and later versions.

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:


Fig. 1


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:


Fig. 2


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:

#,##0.00[Green];(#,##0.00)[Red];"Zero";"Null"


Fig. 3


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:


Fig. 4


Notice the result when focus is set to the Discount textbox for the first record:


Fig. 5


Notice the result when focus is set to the Discount textbox for the third record (i.e., the negative discount):


Fig. 6


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:


Fig. 7


Fig. 8


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. 9


Fig. 10 shows the final result:


Fig. 10


Created: Jan. 17, 2005
Last Updated: Apr. 24, 2008

Copyright © 2004 - 2008 Tom Wickerath. All rights reserved. Reprinted at vb123.com by permission.

About the author:

Tom Wickerath is a chemist at The Boeing Company in Seattle, Washington, USA. He works in the Analytical Chemistry Group of Boeing's Materials & Process Technology (M&PT) organization. In the early 1990's, Tom became interested in the use of database technology to avoid many of the inefficiencies involved in using spreadsheets for the analysis of large amounts of data.

Tom has taught Microsoft Access courses to college students for three years at Bellevue Community College in Bellevue, Washington. He has also been an active member of the Pacific Northwest Access Developer's Group (PNWADG) since the days of Access 2.0 and served as an officer of this group from 1998 to 2002. As a longtime Access expert, Tom has given presentations on complex Access subjects, including tools designed for Access developers, to the Seattle Access Group, the PNWADG and to Boeing Company personnel.

Tom has provided his expertise to Microsoft Access developers and users for years in the Microsoft Access Newsgroups in UseNet, and he was awarded MVP for Microsoft Access in April, 2006. Check the Microsoft.Public.Access Newsgroup for Tom's latest expert advice and feel free to post any questions in these Newsgroups.

Special Note From The Author: "If you find that any of these tips save you a lot of time and frustration with your database development, please consider making a tax deductible contribution to the Northwest Kidney Center, located in Seattle, Washington, USA. I've seen first hand how valuable their work is. With your help, they can do more research in the treatment and prevention of kidney disease, provide increased financial assistance to patients in need, and generally make kidney patients' lives more comfortable. You can even use PayPal to make a quick and easy donation online, and you'll feel better knowing that you have contributed to a better quality of life for those in need. Thanks."

-- Tom Wickerath

http://www.nwkidney.org/nkc/howYouCanHelp/donate/index.html

For questions regarding this tutorial, please contact Tom at:



Tom does not accept unsolicited requests for help. Contact Tom only if you have questions or feedback on one of his articles or tips, or you have been specifically invited by Tom in a newsgroup posting to contact him. If your question references a question in the newsgroups, please include the URL to the post.

Links >>>  Home | Search | Workbench | Orders | Newsletter | Access Security | Access professionals