Syntax for Use of Yes/No Field in a Select Statement

[raweber]

I have a query that is supposed to set a value for the STATUS of the record as part of the query. That is, depending on the status of various check boxes and date fields the STATUS field of the query record changes (i.e. from "Study Phase" to "Awaiting Closeout" and several other phases).

I'm not having any difficulties with the dates and use Null values effectively to drive the decision tree. It is when I tried to add in a Yes/No field that I can't get the syntax right. The field label is "IM" and here are a few of the things I've tried:

IIf(([IM])=-1,...)
IIf(([IM])=False,...)

The ellipsis marks the THEN/ELSE portion of the IIf statement. Basically, all I'm looking to do is incorporate the value of a check box into the decision tree.

Anyone know how to do this properly?

Thanks, Rob

[JoeM]

If IM is truly a Yes/No field, it is stored as Boolean, so Yes=True and No=False.
So, you should just be able to write it like:

IIF([IM],what to return in IM is Yes,what to return if IM is No)

If you are still having issues, post your entire equation here so we can see if there are any other issues with it.

[raweber]

Ahh, that was simple. Now if only there was something that helped you count the number of parentheses in these huge nested IIf statements to make debugging easier.

Thanks!
Rob

[JoeM]

You are most welcome!

If you have huge nested IIF statements, you may want to consider converting it to a User Defined Function (UDF) in VBA instead.
They are much easier to maintain, and if you are using the formula in more than one place, you only have to update one function instead of all the formulas.