Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-02-2019, 09:10 PM   #1
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 484
Thanks: 287
Thanked 0 Times in 0 Posts
access2010 is on a distinguished road
Comments Field, Conditional Formatting

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

access2010 is offline   Reply With Quote
Old 06-03-2019, 12:58 AM   #2
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,315
Thanks: 431
Thanked 787 Times in 764 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Comments Field, Conditional Formatting

Try
Code:
 =NZ(LEN(control),0) > 0
You might not need the NZ function. Try without first. ?

HTH
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 05:17 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,644
Thanks: 56
Thanked 1,244 Times in 1,225 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Comments Field, Conditional Formatting

Hi Nicole. One other thing you could try, maybe
Code:
Field Value Is > Equal > Null
Hope it helps...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 04:27 PM   #4
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 484
Thanks: 287
Thanked 0 Times in 0 Posts
access2010 is on a distinguished road
Re: Comments Field, Conditional Formatting

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
Attached Files
File Type: mdb Conditional_Formating_Comments.mdb (436.0 KB, 7 views)
access2010 is offline   Reply With Quote
Old 06-03-2019, 04:57 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,901
Thanks: 114
Thanked 2,981 Times in 2,712 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Comments Field, Conditional Formatting

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

Attached Images
File Type: jpg Capture.jpg (70.7 KB, 71 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin (Mendip Data Systems)
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 05:21 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,644
Thanks: 56
Thanked 1,244 Times in 1,225 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Comments Field, Conditional Formatting

Quote:
Originally Posted by access2010 View Post
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 > ""
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 05:25 PM   #7
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,046
Thanks: 10
Thanked 208 Times in 197 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Comments Field, Conditional Formatting

Quote:
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.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 06:05 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,643
Thanks: 97
Thanked 1,499 Times in 1,414 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Comments Field, Conditional Formatting

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

How about:

Expression Is | [controlname] is not null
Galaxiom is online now   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 06:28 PM   #9
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,046
Thanks: 10
Thanked 208 Times in 197 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Comments Field, Conditional Formatting

The difference likely being that in continuous or datasheet view, the new record field should also be highlighted whereas Len() won't.
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 06:49 PM   #10
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,644
Thanks: 56
Thanked 1,244 Times in 1,225 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Comments Field, Conditional Formatting

Quote:
Originally Posted by Galaxiom View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 07:22 PM   #11
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,046
Thanks: 10
Thanked 208 Times in 197 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Comments Field, Conditional Formatting

Quote:
Originally Posted by theDBguy View Post
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."
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 07:30 PM   #12
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 484
Thanks: 287
Thanked 0 Times in 0 Posts
access2010 is on a distinguished road
Re: Comments Field, Conditional Formatting

Thank you, isladogs
Your suggestion works.

Crystal
access2010 is offline   Reply With Quote
Old 06-03-2019, 07:37 PM   #13
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,644
Thanks: 56
Thanked 1,244 Times in 1,225 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Comments Field, Conditional Formatting

Quote:
Originally Posted by Micron View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
access2010 (06-03-2019)
Old 06-03-2019, 08:04 PM   #14
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 484
Thanks: 287
Thanked 0 Times in 0 Posts
access2010 is on a distinguished road
Re: Comments Field, Conditional Formatting

theDBguy

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

Field value is = not equal to = ""

Nicole

access2010 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question How to set conditional formatting for a field just once? pdipietro General 3 04-13-2019 01:22 AM
Conditional formatting of field clone4 Forms 1 02-08-2009 09:09 AM
Conditional Formatting for One Field Based on Value of a (Different) Date field kjohns Forms 5 05-05-2008 08:01 AM
Conditional Formatting to another field alunkane Forms 1 11-17-2004 11:30 AM
[SOLVED] Conditional Formatting on another Field GKuenning Reports 2 04-01-2004 11:48 AM




All times are GMT -8. The time now is 03:09 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World