By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,218 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

IIF in Query Design

P: n/a
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

Feb 28 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
<bs********@yahoo.com> wrote in message
news:11*********************@j33g2000cwa.googlegro ups.com...
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

Feb 28 '06 #2

P: n/a
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

ja********@gmail.com
Access/Excel VBA Developer
http://code.comparative-advantage.com/

Feb 28 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.