I have been puzzling for some time why a Microsoft Access continuous form in an application I am maintaining insisted on replacing the background colour of a control by making it transparent when the record was not selected, and although the background colour returned when actually selecting the record the conditional formatting that was supposed to have been applied failed to do so . Only today have I found the reason, and so I thought I would share it with you here.
I am working with a calendar table full of appointments. I have a field in a record that indicates the outcome of the appointment. Normal values are NULL or “ATT” (where an appointment starts as NULL, but if the appointment is attended the marker is set to “ATT”). If this marker has any other value, something special has occurred, and I am trying to apply conditional formatting to turn the foreground of the field blue.
A simple expression to determine if a value is not the two values mentioned above is this:-
NZ(outcome,”ATT”) <> “ATT”
and that is the expression I used in the conditional formatting, applying to almost all the fields in the form, with the type of conditional formatting set to “Expression Is”.
The users of this application are all using Access 2000, and when they display the form they reported a strange problem. When the form is displayed all of the fields initially appear greyed out or transparent. Only when you click on the specific field does it change to the more normal white background. Furthermore the conditional formatting that should kick in if the value is not correct doesn’t appear to work.
To make matters worse, I was not experiencing this issue on the version of Access I develop with (Access 2010).
It turns out that this expression is the root of the problem. If I change it to the somewhat more verbose
outcome is not null and outcome <> “ATT”
The problems all disappear.
I have searched the net for any indication of this problem, and couldn’t find any reference to it. If you have too – now you know the answer.