Conditional formatting of a report field based on outcome of two fields (Access 2016) (2 Viewers)

Suzi

New member
Local time
Today, 15:28
Joined
Aug 29, 2019
Messages
4
Hello everyone!

Long time reader, first time poster!

I have done several searches to find the answer I need to my conditional formatting nightmare but I can't find an example which matches mine and my own thoughts aren't yielding anything.

I have two fields: "car reg" and "external". If car reg is blank and external is true I want the car reg field on a report to have a shaded background.
I've tried many different ways and none have worked.

Currently, in my conditional formatting box I have:

Expression is: ([tblContacts.External]=True) And ([tblContacts.Car Reg] Is Null)

but no amount of changing 'true' to Is not null, 1, "true" and all of the other iterations I can think of changes the colour of the cells! :banghead:

Please could any of you super helpful people put me out of my misery? :eek:

Suzi
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:28
Joined
Sep 21, 2011
Messages
14,048
I have never used the table reference and field, always the control names.?

Code:
[Sum of Amount]>100
 

Suzi

New member
Local time
Today, 15:28
Joined
Aug 29, 2019
Messages
4
I have never used the table reference and field, always the control names.?

Code:
[Sum of Amount]>100

Thanks Gasman, Unfortunately the field aren't numerical; one is a true/false and the other is a text field. I simply need to know if the true/false is true AND the car reg field is blank that it highlights the field on the report to me to remind me to get the reg number.

It's probably the car reg value that's causing the expression to not work. How do we represent a null value in an expression?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:28
Joined
Sep 21, 2011
Messages
14,048
Don't be concerned about the type of control, that was just an example.?
Test the controls on the report not the table fields.

You could use IsNull() or Nz() functions?

Code:
Not IsNull([Sum of Amount])
 

Suzi

New member
Local time
Today, 15:28
Joined
Aug 29, 2019
Messages
4
Sorry Gasman, I'm not really sure what you mean.

here is a screenshot of what I have:
 

Attachments

  • conditional formatting1.PNG
    conditional formatting1.PNG
    10.8 KB · Views: 111

Gasman

Enthusiastic Amateur
Local time
Today, 22:28
Joined
Sep 21, 2011
Messages
14,048
I've already stated that I have never used the field names, only control names.
Copy that code you already have and paste into notepad or anywhere you can save the old version.
Then use the control names and try that.

if that does not work, you can always paste the old formula back, should you wish.?
 

Suzi

New member
Local time
Today, 15:28
Joined
Aug 29, 2019
Messages
4
Okay thank you, I get that you're saying I'm using the wrong wording but where do I find what the control name is please...?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:28
Joined
May 7, 2009
Messages
19,169
open your report in design view.
click on the [car reg] or [extrenal] textbox.
on its property->others, you will see their Name.
try renaming them also, txtCarReg and txtExternal.

on your CondFormat Expression:

(Nz([txtCarReg], "") = "") And Nz([txtExternal], False)
 

Users who are viewing this thread

Top Bottom