IIf statement sometimes works

[Donna]

I have the following IIf statement on an unbound field on a form:

=IIf([Loaned/Out]="0"," ","Out of Service - Please Exit and select another
Vehicle")

Sometimes it comes back with the correct "Out of Service..." or nothing if
the vehicle is not designated as loaned/out, but other times it comes back
with "#NAME?".

Why is it not consistent? Thanks.

 

[golfinray]

1. If [loaned/out] is ever null you will get #name#. 2. Is [loaned/out] ever
not equal to "0"? Is "0" text or a number? If it is a number, you don't need
the quotes.

 

[Donna]

Golfinray:

Thanks for the info. In the table, it is a Yes/No check box. I could not
get the IIf statement to work using "YES" or "NO", so I went with "0" = NO
and "-1" = YES because it would then run the statement correctly when I had
the check box selected. Am I assuming something that is not correct?

Thanks.

 

[John W. Vinson]

Golfinray:

Thanks for the info. In the table, it is a Yes/No check box. I could not
get the IIf statement to work using "YES" or "NO", so I went with "0" = NO
and "-1" = YES because it would then run the statement correctly when I had
the check box selected. Am I assuming something that is not correct?

A Yes/No field in a table - however it's formatted or displayed - is indeed
stored as a Number, 0 for No, -1 for Yes.

Comparing a -1 to a text string "Yes", or even to a text string "-1" (note the
quotes!) won't give the desired result. However, there are SQL defined
constants Yes and No (no quotes) which are equal to -1 and 0.

In an IIF statement, remember that the first argument to IIF is an expression
which evaluates to True or False. You may be able to just use the yes/no field
itself:

IIF([yesnofield], "value if yes", "value if no")

will work and you won't need any comparison operator.