Conditional formatting and expressions?

[Whizzo]

Hi all;

Any reason why putting:

Len([COMP Policy Number])<Len([PolicyNumber])

into the Conditional Formatting of a control isn't applying it in a report? Even when the length of [COMP Policy Number] is indeed shorter that the length of [Policy Number]? I've tried a few other simple expressions in Conditional Formatting too, such as:

[[I]an empty field][/I] = ""

[[I]an empty field][/I] = null

and so on but nothing seems to be applying my formatting. Is there any special way of approaching this kind of thing I have to consider?

Thanks!

[ChipR]

It doesn't seem like you can do the Len operation on a number. Try

Len(Format([NumericField]))

[NeoPa]

There are two ways to check for Null (which is certainly not equal to "" - empty string - in Access) :

1. If IsNull([Field]) Then ... in VBA
2. ...[Field] Is Null... in SQL

It is never valid to use [Field] = Null. That will never resolve to True.

I can't see an obvious reason why your code wouldn't work, but there is not too much information available to work from.

If I were to guess, I would say that you are maybe referring to the controls on the report rather than the underlying fields. From my reading of the matter in help, although not very specific, it implies fields only are available to check.

Perhaps you could experiment to determine which values are available and which not.

[Whizzo]

Len(Format([NumericField]))

That worked great, thanks! I'll try and get a bit more used to it. How come format() worked? Looking at the function help it seems more concerned with formatting dates and times. I was going to use CStr() instead but now it looks like I won't need to.

[ChipR]

Format always returns a string. I just picked that up myself from a post by NeoPa the other day :)

[NeoPa]

Good spot Chip. I would have expected the parameter of Len() to be converted automatically as part of the compilation, but no.

How come format() worked? Looking at the function help it seems more concerned with formatting dates and times.

The Format() function is very flexible and powerful, but if no format parameter supplied, will default to a very basic format string (internationall y aware).

CStr() would have worked equally well. It seems it was simply looking for a string value rather than a numeric one.