Conditional Formatting A Datasheet

[TheRadioboy]

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

 

[isladogs]

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

 

[TheRadioboy]

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]

The first reply explained what you need to do

 

[TheRadioboy]

Sorry, my ineptitude in not reading your reply properly - many thanks again!

 

[isladogs]

No problem.
Presumably black text on black background so its 'redacted'

 

[TheRadioboy]

Yes that's right - simply to show that it's not applicable when the 'S&P' criteria is shown in the 'DEPT' field.

 

[TheRadioboy]

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

 

[isladogs]

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]

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]

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]

Use Expression:

[Dept] like "S[&]P"

 

[TheRadioboy]

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]

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]

Thank you, Ridders and Arnel - I'll try that!

 

[TheRadioboy]

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]

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.