Re: Conditional Format problems

On Sun, 29 Apr 2007 13:43:05 +0200, RoyVidar wrote:

1) Using Access:
I select "Field value is" and "equal to". Not sure what to put in the
Expression1 field. Have tried leaving it empty, inserting a pair of
double quotes (""), a pair of single quotes ('') and the word Null.
None of these work. The condition which I have selected (not enabled)
does not happen. I am sure that there must be a simple solution that
I am overlooking.

2) Using VBA:
With my spectacular failure to get 1) working, I turned to VBA. Tried
to apply a FormatCondition to the textbox. As I understand it, the
textbox has a property FormatConditions which refers to a
FormatConditions collection for the textbox. I have tried to use the
Add method to add my condition - eg:

With Me.SubformName!txtBox.FormatConditions
Debug.Print .Count 'prints "1" due to attempt 1) above
.Delete
Debug.Print .Count 'prints "0" - successful delete
.Add acFieldValue, acEqual, Null
'(etc)


I think I'd use 1)

Choose "Expresssion is", then as the expression, enter

Len([NameOfYourField] & "") = 0

which should format both when ZLS and Null.

Thanks Roy. This worked, although I had to massage it a bit first. Using
[TableColumnName] didn't work for some reason. And when I tried Len(txtBox
& "") = 0, Access kept putting doublequotes around "txtBox" -- ie,
Len("txtBox" & "") = 0, which means something else entirely. Same thing
happened when I tried qualifying the controlname in various ways (such as
Me!txtBox). Eventually got this version working:
Len(txtBox.Value & "") = 0

I am still interested in learning how the VBA version works (for future
reference). If anyone can point me at a good tutorial on Conditional
Formatting in VBA, it would be much appreciated. I like to know how things
work!

Thanks again.
Lyn.
.