Conditional Formatting A Datasheet (1 Viewer)

TheRadioboy

Registered User.
Local time
Today, 09:07
Joined
Sep 27, 2017
Messages
20
Hello!

Could someone please give a bit of help here? I'm using Access 2016.

I wish to apply some formatting to a datasheet on a split form.

I have a field called 'DEPT' and two others called 'EMAILED' and 'CLEARED' - what I want to build is an expression that means if the 'DEPT' field has a set result shown, in this case the department is 'S&P' then the 'EMAILED' and 'CLEARED' field should be coloured black.

How do I build this expression please?

Thanks :eek:
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 09:07
Joined
Jan 14, 2017
Messages
18,186
I have a field called 'DEPT' and two others called 'EMAILED' and 'CLEARED' - what I want to build is an expression that means if the 'DEPT' field has a set result shown, in this case the department is 'S&P' then the 'EMAILED' and 'CLEARED' field should be coloured black.

Its not clear whether you mean the text should be black or the field background should be black.
Either way, open the conditional formatting wizard, select the 'Emailed' field then New Rule

Change the dropdown to 'Expression Is' then enter [Dept]="S&P"
Add the formatting you require then click OK



Repeat for 'CLEARED' field

NOTE: It is possible to do both field at the same time if the CF is identical
 

Attachments

  • CFWizard.PNG
    CFWizard.PNG
    11 KB · Views: 746

TheRadioboy

Registered User.
Local time
Today, 09:07
Joined
Sep 27, 2017
Messages
20
Thanks Ridders, your help is much appreciated.

It's the background colour I want to change. So I actually want to colour the EMAILED & CLEARED fields together if the 'DEPT' field is 'S&P'. Is that possible?

Sadly - as I am bit of a dullard with Access at the moment - I don't know how to format a full expression for this if you may be able to help.

Many thanks,
Rob :)
 

isladogs

MVP / VIP
Local time
Today, 09:07
Joined
Jan 14, 2017
Messages
18,186
The first reply explained what you need to do
 

TheRadioboy

Registered User.
Local time
Today, 09:07
Joined
Sep 27, 2017
Messages
20
Sorry, my ineptitude in not reading your reply properly - many thanks again!

:)
 

isladogs

MVP / VIP
Local time
Today, 09:07
Joined
Jan 14, 2017
Messages
18,186
No problem.
Presumably black text on black background so its 'redacted'
 

TheRadioboy

Registered User.
Local time
Today, 09:07
Joined
Sep 27, 2017
Messages
20
Yes that's right - simply to show that it's not applicable when the 'S&P' criteria is shown in the 'DEPT' field.
 

TheRadioboy

Registered User.
Local time
Today, 09:07
Joined
Sep 27, 2017
Messages
20
I have filled in the expression as such, however it doesn't show up in the table - as shown in the two files attached.

I presume I am doing something wrong here?

Rob :)
 

Attachments

  • DB-Emailed.PNG
    DB-Emailed.PNG
    9.5 KB · Views: 220
  • Table Result.PNG
    Table Result.PNG
    7.2 KB · Views: 207

isladogs

MVP / VIP
Local time
Today, 09:07
Joined
Jan 14, 2017
Messages
18,186
Previously you wrote 'S&P'
The CF wizard can cope with spaces e.g. "S and P" so it should work.
Perhaps you have a double space?

Try copying the field value from the table to the CF wizard
 

TheRadioboy

Registered User.
Local time
Today, 09:07
Joined
Sep 27, 2017
Messages
20
I changed it from 'S&P' as the ampersand wouldn't show properly in the CF wizard.

I've tried what you suggested and it still doesn't colour the box, unfortunately.

Many thanks for your time and help though.

:)
 

isladogs

MVP / VIP
Local time
Today, 09:07
Joined
Jan 14, 2017
Messages
18,186
I've tried it using a different db with both '&' and with ' and '

Both worked fine for me
In general you'll have fewer issues if you avoid spaces.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:07
Joined
May 7, 2009
Messages
19,170
Use Expression:

[Dept] like "S[&]P"
 

TheRadioboy

Registered User.
Local time
Today, 09:07
Joined
Sep 27, 2017
Messages
20
I've tried it using a different db with both '&' and with ' and '

Both worked fine for me
In general you'll have fewer issues if you avoid spaces.

LOL, it's clearly me! I think I need medical attention!

Thanks Ridders ... I'll step away from the database for a bit and return later, might see my error more clearly then.

:)
 

isladogs

MVP / VIP
Local time
Today, 09:07
Joined
Jan 14, 2017
Messages
18,186
Apologies - my mistake
Special characters like '&' can also cause problems
'&' is being used to indicate something else in the CF wizard.
You need to do what arnel said if you want to use '&'

Alternatively how about changing the dept name to SP?
 

TheRadioboy

Registered User.
Local time
Today, 09:07
Joined
Sep 27, 2017
Messages
20
Had a thought - the 'DEPT' field is generated from a 'look-up' list (which has it's own table to create the list) - will that impact the CF on the table?

In my data entry it's simply a drop down list that I use to indicate the department.

Does CF only work on manually entered text?
 

isladogs

MVP / VIP
Local time
Today, 09:07
Joined
Jan 14, 2017
Messages
18,186
AFAIK the CF wizard will only work with values entered manually

It is possible to do CF using VBA but it needs a bit more skill to do so.
 

Users who are viewing this thread

Top Bottom