Comments Field, Conditional Formatting

[access2010]

Hello,
We have a comments field in our MsAccess 2003 data base and would like to have a Colour Background if it contains any data.

I have tried = Field Value is = Greater Than = 0

Without any success.

Could I please have an other suggestion.

Thank you, Nicole

 

[Gasman]

Try

 =NZ(LEN(control),0) > 0

You might not need the NZ function. Try without first. ?

HTH

 

[theDBguy]

Hi Nicole. One other thing you could try, maybe

Field Value Is > Equal > Null

Hope it helps...

 

[access2010]

Thank you, Gasman and theDBguy.

Your suggestions work in other data fields other than the COMMENT field.
If you have an opportunity could you suggest an other Conditional Format code to try?

Thank you.
Crystal

 

[isladogs]

Just tried in both a text and a memo field in A2003
Field Value not equal to Null doesn't work
However Field Value not equal to "" works for me in both form view & datasheet view

 

[theDBguy]

Thank you, Gasman and theDBguy.

Your suggestions work in other data fields other than the COMMENT field.
If you have an opportunity could you suggest an other Conditional Format code to try?

Thank you.
Crystal

Hi Crystal. I guess based on what Colin said, you could add a second rule like:

Field Value Is > Equal > ""

 

[Deleted member 147267]

Just tried in both a text and a memo field in A2003

not sure I understand what "both" was?
Checking the length does work in continuous and datasheet view, rich or plain text for control, and table field is memo (long text).
Could play with posted db but didn't download it, preferring expression that 'didn't work' so as to maybe avoid doing that. What I used:

Len([textControlName])>0


Nz is not required as a conversion isn't needed for Len function in this case.

 

[Galaxiom]

Equals Null is unlikely since Nulls don't usually relate to equal.

How about:

Expression Is | [controlname] is not null

 

[Deleted member 147267]

The difference likely being that in continuous or datasheet view, the new record field should also be highlighted whereas Len() won't.

 

[theDBguy]

Equals Null is unlikely since Nulls don't usually relate to equal.

How about:

Expression Is | [controlname] is not null

Good point. Maybe something like this then?

Field Value Is > Equals > Is Null

Otherwise, forget it since the other approaches definitely work anyway. I couldn't do any testing to try it out. Sorry.

 

[Deleted member 147267]

Good point. Maybe something like this then?

Field Value Is > Equals > Is Null

Nope
"The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or identifier."

 

[access2010]

Thank you, isladogs
Your suggestion works.

Crystal

 

[theDBguy]

Nope
"The expression you entered contains invalid syntax.
You may have entered a comma without a preceding value or identifier."

Thanks. Never mind then. I couldn’t do any testing. Sorry.

 

[access2010]

theDBguy

Thank you for trying to help us, we are now using the formatting below which works.

Field value is = not equal to = ""

Nicole