IIF in Query Design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

bshort1023@yahoo.com - 28 Feb 2006 16:34 GMT

I created a query based on query. I am creating a new field in the
query called "Series". The field is created as follows:

Series: IIf([ITEM_NBR] Like "11-2*","11-2XX")

For all ITEM_NBR that are 11-201 or 11-202 etc make the value in the
Series field 11-2XX. This works fine. The problem is when I try to
add a 2nd part to this expression such as:

Series: IIf([ITEM_NBR] Like "11-2*","11-2XX") Or IIf([ITEM_NBR] Like
"11-3*","11-3XX")

This query runs but instead of putting a value of 11-2XX or 11-3XX in
the Series field I get a -1 for everything that should be 11-2XX or
11-3XX.

I have tried many different variations on this with no success. Any
help would be greatly appreciated.

bshort1023

Reply to this Message

tina - 28 Feb 2006 16:43 GMT

try a nested IIf() function, as

Series: IIf([ITEM_NBR] Like "11-2*","11-2XX", IIf([ITEM_NBR] Like
"11-3*","11-3XX", Null))

it's a good idea to always include the "Else..." argument in the function,
which is why i added the "Null" value.

hth

> I created a query based on query. I am creating a new field in the
> query called "Series". The field is created as follows:
[quoted text clipped - 16 lines]
>
> bshort1023

Reply to this Message

james.igoe@gmail.com - 28 Feb 2006 16:51 GMT

Series: IIf([ITEM_NBR] Like "11-2*","11-2XX") Or IIf([ITEM_NBR] Like
"11-3*","11-3XX")

Your equation is incorrect. Correctly, IIF is read left to right, so
it evaluates the statement, and if true it writes 11-2XX, else if next
is true it writes 11-3XX, else, [ITEM NBR], or whatever should be
written if the other two are false, e.g., "".

Series: IIf([ITEM_NBR] Like "11-2*","11-2XX",iIf([ITEM_NBR] Like
"11-3*","11-3XX",[ITEM_NBR]))

Pardon any inaccuracies in parsing, but this should be work.

James Igoe

james.igoe@gmail.com
Access/Excel VBA Developer
http://code.comparative-advantage.com/

Reply to this Message