Conditional Formatting form field based on Yes or No

[Raddle]

Hi

Does anyone know a neat way to highlight a field on a form if it has Yes or No in it? Obvs Fill Green if yes, Fill Red if no?

I currently have this which a picked from a Google search. I can get it to go Green if yes not not Red if no.

InStr([MyFieldName],"Yes")>0



but if I add a second condition for No, it doesn't seem to like it.

Any hints?

Thanks

[JoeM]

You added a second rule, and didn't overwrite the first one, right?

What does your data look like?
Note that InStr is case-sensitive. So "No" is not the same as "no".
Also note that you could get false positives, if it is part of a larger word (i.e. like "Snow", "Now", etc).

[Micron]

Note that InStr is case-sensitive. So "No" is not the same as "no".

Not necessarily true. If the parameter is omitted, it defaults to your Option Compare statement. That may or may not be binary. I'd say in most cases it is not.
@Raddle, post more than one line of code so we can see what you have. Please use code tags and indentation for more than a few lines (# on posting toolbar). Is the yes or no buried within other words or is it a single value?

[Raddle]

Thanks for the hints and the time to reply.

So embarrassingly that is the whole 'code' ....

In form design, I select the field and choose conditional formatting and then add a rule and paste that in. Obvs the field name is real in my version.

And this works for a single condition. However I need it to work to for when there is a 'No' in the field value.

Yes I added a second rule with the No condition and that does not solve it.

************ having looked again at the case sensitive element, with the second rule, that seems to solve it. Thank you *********

[Micron]

You will also turn the field red if it contains the word "Notes", so add a space to the test string?
I incorrectly assumed you were using vba to do the CF.

EDIT - In fact, with just a trailing space I suspect a string like " The rhino was huge." would also turn red, so a leading and trailing space perhaps.
Unfortunately you didn't answer if the yes/no was a single value or was contained in a sentence/string.

[Raddle]

My bad and thank you for coming back to me ...

It should be either Yes or No although it is a short text field, so there could indeed be anything in there about rhinos too.

Will try to frame my questions more accurately going forward. Thanks for your help.