IsNull with AND OR statements in conditional formatting

[Ian Warren]

Hi Guys,

I am trying to use conditional formatting on a textbox that I have turned into a button. I have 3 fields in the background query which relate to the conditional formatting. I want the button (textbox) to be grey if none of them are null, amber if any one or more are null and green if they are all null.

I have tried using conditional formatting with:
IsNull ([Field1]) OR IsNull ([Field2]) OR IsNull ([Field3])
IsNull ([Field1]) AND IsNull ([Field2]) AND IsNull ([Field3])

I have also tried:
If IsNull(Previous_Employer) Then btn_UpdatePrevious2.BackColor = 52479

and that doesn't even work in the Form Activate event and even for 1 field that doesn't work.

Is this possible and where can I go to find out how to do it? I am happy to read up on it, that way I will learn what I'm doing for next time.

Thanks for your help.

 

[missinglinq]

Gray
Expression Is Not IsNull([field1]) And Not IsNull([field2]) And Not IsNull([field3])

Green
Expression Is IsNull([field1]) And IsNull([field2]) And IsNull([field3])

Amber
Expression Is IsNull([field1]) Or IsNull([field2]) Or IsNull([field3])

 

[Ian Warren]

I have tried this in Conditional Formatting but it doesn't work it, just stays as one colour, I'm not sure how to get this to work and what the expression should be.

I tried both of these myself already:
Is IsNull([field1]) And IsNull([field2]) And IsNull([field3])
Is IsNull([field1]) Or IsNull([field2]) Or IsNull([field3])

The conditonal formatting doesn't seem to recognise 'And' or 'Or', any ideas why?

 

[Brianwarnock]

Conditional formatting does recognise And and Or, The quoted expressions by Linq should work although he confuses the issue with Expression ... for the first and Expression Is ....for the others but I'm sure that you understand what he is saying.

I have just tested And and Or

Brian

 

[missinglinq]

Sorry, omitted the Is of Expression Is for the Gray statement when copying and pasting the CF statement. Go back to Post #2 for the correction, in bold.

As Brian says, the code is valid, and works for me just as you asked. I will say this, however, I've had problems in the past, where I got incorrect formatting from CF, deleted the old Expression and entered a corrected Expression, and the old formatting persisted. I finally deleted the control that was having CF applied to it, then re-created it and applied the new Expressions.

It works for me, as it should for you.

 

[Ian Warren]

I have tried this and it doesn't work for me. I adapted the code slightly from what was above as I don't feel you need to specifically state that if they are all completed that it should be green. With the other two lines there is no other choice!

The problem I'm getting with this is that it is always green though.

I'm going to delete the control and try again as suggested but if I have made a mistake with this code please let me know.

 

[Ian Warren]

I tried again using the attached but now it is just permanently grey, is there a way to code this outside the conditional formatting as it really doesn't want to play ball!

 

[missinglinq]

It's not that Conditional Formatting doesn't want to play ball; it just insists that when it plays ball, the rules be followed! The problem is that you have not followed the directions in Post #2 ! The code you have for Amber is the only code that you have entered correctly!

Gray should be

Expression Is [B]Not[/B] IsNull([Previous_Employer]) And [B]Not[/B] IsNull([LT_Environment]) And [B]Not[/B] IsNull([LT_Notice])

Green should be

Expression Is [B]IsNull[/B]([Previous_Employer]) And [B]IsNull[/B]([LT_Environment]) And [B]IsNull[/B]([LT_Notice])

You could simply set your the Green as the Default; I really included it in an effort to demonstrate all the possibilities you asked for. Not necessarily for you, but for others who may come upon this post when researching similar problems.

And in answer to your question, "No." In a Single View form, you could do all of this thru VBA code, but in a Datasheet View or Continuous View form, it has to be done thru Conditional Formatting.

BTW, the screen shots were very helpful! In a forum like this, not being able to actually see the OP's screen is one of the problems in trying to troubleshoot databases.