Comments Field, Conditional Formatting (1 Viewer)

access2010

Registered User.
Local time
Today, 16:19
Joined
Dec 26, 2009
Messages
1,019
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

Enthusiastic Amateur
Local time
Today, 23:19
Joined
Sep 21, 2011
Messages
14,048
Try
Code:
 =NZ(LEN(control),0) > 0

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

HTH
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:19
Joined
Oct 29, 2018
Messages
21,358
Hi Nicole. One other thing you could try, maybe
Code:
Field Value Is > Equal > Null
Hope it helps...
 

access2010

Registered User.
Local time
Today, 16:19
Joined
Dec 26, 2009
Messages
1,019
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
 

Attachments

  • Conditional_Formating_Comments.mdb
    436 KB · Views: 104

isladogs

MVP / VIP
Local time
Today, 23:19
Joined
Jan 14, 2017
Messages
18,186
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

 

Attachments

  • Capture.jpg
    Capture.jpg
    70.7 KB · Views: 248

theDBguy

I’m here to help
Staff member
Local time
Today, 16:19
Joined
Oct 29, 2018
Messages
21,358
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:
Code:
Field Value Is > Equal > ""
 

Micron

AWF VIP
Local time
Today, 19:19
Joined
Oct 20, 2018
Messages
3,476
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

Super Moderator
Staff member
Local time
Tomorrow, 10:19
Joined
Jan 20, 2009
Messages
12,849
Equals Null is unlikely since Nulls don't usually relate to equal.

How about:

Expression Is | [controlname] is not null
 

Micron

AWF VIP
Local time
Today, 19:19
Joined
Oct 20, 2018
Messages
3,476
The difference likely being that in continuous or datasheet view, the new record field should also be highlighted whereas Len() won't.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:19
Joined
Oct 29, 2018
Messages
21,358
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?
Code:
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.
 

Micron

AWF VIP
Local time
Today, 19:19
Joined
Oct 20, 2018
Messages
3,476
Good point. Maybe something like this then?
Code:
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

Registered User.
Local time
Today, 16:19
Joined
Dec 26, 2009
Messages
1,019
Thank you, isladogs
Your suggestion works.

Crystal
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:19
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 16:19
Joined
Dec 26, 2009
Messages
1,019
theDBguy

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

Field value is = not equal to = ""

Nicole
 

Users who are viewing this thread

Top Bottom