Thursday, January 16, 2014

My Favourite Conditional Format + Listing Conditional Formats

My favourite conditional format is one we use in our invoicing system that highlights the old invoices that haven't been paid. This shows invoices that are a month overdue with a purple box, invoices that are 6 weeks old with a red box and invoices that are 6 weeks old where we have had a follow up conversation with a yellow box.  See this in figure 1. In figure 2, I illustrate the conditional formatting expressions for the coloured box.

Figure 1 - Highlight mature invoices with Conditional formatting

Figure 2 -Conditional formatting expressions for the coloured box

Multiply conditional formatting 10 times and what do you have ?

As you can see in the figure 2, conditional formatting can get out of hand really quickly and you soon will wish that you could document and program conditional formatted fields in VBA. We found out this week that we couldn't find an elusive piece of code to document the actual expression in the conditional format equation. Finally after much digging, I found the code in the sample database that Rick Dobson submitted for his 2005 Smart Access article on Conditional Formatting.  I have now published that elusive code in this new article on how to list conditional format expressions

and to get into this, you will need to download the Access 2007 sample database here

Tip: To make a colourful highlight box as in figure 1, put the forms index field in second text field and lock it. The conditional format will need to show Red text on a Red background for the Red condition. You can work out the rest.


grovelli said...

Hi Garry, great article and thanks for your effor in improving on it.
The form frmDatesForOrders on the sample database DobsonSA1004.accdb has 3 buttons labelled "List Conditional Formats" but when you click them nothing happens and I don't see an explanation of their purpose in the article.

grovelli said...

Sorry, found it:
"In figure 1 is a button that lists the conditional formats into the Immediate Window"