Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Reports (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=10)
-   -   Conditional formatting of a report field based on outcome of two fields (Access 2016) (https://www.access-programmers.co.uk/forums/showthread.php?t=306599)

Suzi 08-29-2019 04:09 AM

Conditional formatting of a report field based on outcome of two fields (Access 2016)
 
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? :o

Suzi

Gasman 08-29-2019 04:33 AM

Re: Conditional formatting of a report field based on outcome of two fields (Access 2
 
I have never used the table reference and field, always the control names.?

Code:

[Sum of Amount]>100

Suzi 08-29-2019 07:03 AM

Re: Conditional formatting of a report field based on outcome of two fields (Access 2
 
Quote:

Originally Posted by Gasman (Post 1637845)
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 08-29-2019 07:36 AM

Re: Conditional formatting of a report field based on outcome of two fields (Access 2
 
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 08-30-2019 03:27 AM

Re: Conditional formatting of a report field based on outcome of two fields (Access 2
 
1 Attachment(s)
Sorry Gasman, I'm not really sure what you mean.

here is a screenshot of what I have:

Gasman 08-30-2019 05:43 AM

Re: Conditional formatting of a report field based on outcome of two fields (Access 2
 
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 08-30-2019 06:16 AM

Re: Conditional formatting of a report field based on outcome of two fields (Access 2
 
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 08-30-2019 07:58 AM

Re: Conditional formatting of a report field based on outcome of two fields (Access 2
 
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)


All times are GMT -8. The time now is 09:10 AM.

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