Multiple IIf Statements Not Working

A

AccessIM

I am trying to create a multiple IIf statement and it just isn't working.

I want to base the contents of one field ([Type]) on the contents of two
other fields ([ReserveAisle] and [HandStacked]). Here is the criteria:

If the [HandStacked] field contains a 2 or 3 and the [ReserveAisle]
contains a
13, 14, 21 or 33 I would like the [Type] field to say "Hand Stacked"
otherwise it
should say "Pallet".

Here are two IIf statements I have tried but don't work:

IIf([HandStacked]>1,IIf([HandStacked]<4,IIf([ReserveAisle]=13,IIf
([ReserveAisle]=14,IIf([ReserveAisle]=21,IIf([ReserveAisle]=33,"Hand
Stacked","Pallet"))))))

IIf([HandStacked]=2 or [HandStacked]=3 and [ReserveAisle]=13 or
[ReserveAisle]=14 or [ReserveAisle]=21 or [ReserveAisle]=33,"Hand
Stacked","Pallet")

Thank you so much!
 
F

fredg

I am trying to create a multiple IIf statement and it just isn't working.

I want to base the contents of one field ([Type]) on the contents of two
other fields ([ReserveAisle] and [HandStacked]). Here is the criteria:

If the [HandStacked] field contains a 2 or 3 and the [ReserveAisle]
contains a
13, 14, 21 or 33 I would like the [Type] field to say "Hand Stacked"
otherwise it
should say "Pallet".

Here are two IIf statements I have tried but don't work:

IIf([HandStacked]>1,IIf([HandStacked]<4,IIf([ReserveAisle]=13,IIf
([ReserveAisle]=14,IIf([ReserveAisle]=21,IIf([ReserveAisle]=33,"Hand
Stacked","Pallet"))))))

IIf([HandStacked]=2 or [HandStacked]=3 and [ReserveAisle]=13 or
[ReserveAisle]=14 or [ReserveAisle]=21 or [ReserveAisle]=33,"Hand
Stacked","Pallet")

Thank you so much!
Click to expand...

In a query?

Parenthesis are often useful to guide Access in how to use the
criteria. I'm sure you know that in math, (2*3)+1 = 7 which is
different than 2*(3+1)= 8.
Criteria works similarly.

NewColumn:IIf(([HandStacked]>1 and [HandStacked]<4 ) and
[ReserveAisle] In (13,14,21,33),"Hand Stacked","Pallet")

or adapting your second example:

NewColumn: IIf(([HandStacked]=2 or [HandStacked]=3) and
([ReserveAisle]=13 or [ReserveAisle]=14 or [ReserveAisle]=21 or
[ReserveAisle]=33),"Hand Stacked","Pallet")
 
A

ANDY-N via AccessMonster.com

AccessIM said:
I am trying to create a multiple IIf statement and it just isn't working.

I want to base the contents of one field ([Type]) on the contents of two
other fields ([ReserveAisle] and [HandStacked]). Here is the criteria:

If the [HandStacked] field contains a 2 or 3 and the [ReserveAisle]
contains a
13, 14, 21 or 33 I would like the [Type] field to say "Hand Stacked"
otherwise it
should say "Pallet".

Here are two IIf statements I have tried but don't work:

IIf([HandStacked]>1,IIf([HandStacked]<4,IIf([ReserveAisle]=13,IIf
([ReserveAisle]=14,IIf([ReserveAisle]=21,IIf([ReserveAisle]=33,"Hand
Stacked","Pallet"))))))

IIf([HandStacked]=2 or [HandStacked]=3 and [ReserveAisle]=13 or
[ReserveAisle]=14 or [ReserveAisle]=21 or [ReserveAisle]=33,"Hand
Stacked","Pallet")

Thank you so much!
Click to expand...

TYPE: IIf([HANDSTACKED]<>2 And [HANDSTACKED]<>3 And [RESERVEAISLE]<>13 And
[RESERVEAISLE]<>14 And [RESERVEAISLE]<>21 And [RESERVEAISLE]<>33,"PALLET",
"HAND STACK")
 
R

Ron2006

You need to add some more parentheses.

The OR is a hard break in any type of if logic. The only way to
change that is to add () to regulate what is to be grouped.

I am writing this with more spaces than should be there in order to
give you the idea.


IIf( ( [HandStacked]=2 or [HandStacked]=3 ) and
( [ReserveAisle]=13 or
[ReserveAisle]=14 or [ReserveAisle]=21 or
[ReserveAisle]=33 ) ,"Hand
Stacked","Pallet")


Ron
 
M

Marshall Barton

AccessIM said:
I am trying to create a multiple IIf statement and it just isn't working.

I want to base the contents of one field ([Type]) on the contents of two
other fields ([ReserveAisle] and [HandStacked]). Here is the criteria:

If the [HandStacked] field contains a 2 or 3 and the [ReserveAisle]
contains a
13, 14, 21 or 33 I would like the [Type] field to say "Hand Stacked"
otherwise it
should say "Pallet".

Here are two IIf statements I have tried but don't work:

IIf([HandStacked]>1,IIf([HandStacked]<4,IIf([ReserveAisle]=13,IIf
([ReserveAisle]=14,IIf([ReserveAisle]=21,IIf([ReserveAisle]=33,"Hand
Stacked","Pallet"))))))

IIf([HandStacked]=2 or [HandStacked]=3 and [ReserveAisle]=13 or
[ReserveAisle]=14 or [ReserveAisle]=21 or [ReserveAisle]=33,"Hand
Stacked","Pallet")
Click to expand...


The second one is close, but you need parenthesis to specify
the order of evaluation for the Ands and Ors.

IIf( (HandStacked=2 Or HandStacked=3)
And (ReserveAisle=13 Or ReserveAisle=14 O
Or ReserveAisle=21 Or ReserveAisle=33),
"Hand Stacked","Pallet")

A slightly easier way to do this particular kind of thing
is:
IIf(HandStacked IN(2,3) And ReserveAisle IN(13,14,21,33),
"Hand Stacked","Pallet")
 
A

ANDY-N via AccessMonster.com

ANDY-N said:
I am trying to create a multiple IIf statement and it just isn't working.
Click to expand...
[quoted text clipped - 18 lines]
Thank you so much!
Click to expand...

TYPE: IIf([HANDSTACKED]<>2 And [HANDSTACKED]<>3 And [RESERVEAISLE]<>13 And
[RESERVEAISLE]<>14 And [RESERVEAISLE]<>21 And [RESERVEAISLE]<>33,"PALLET",
"HAND STACK")
Click to expand...

THIS ONE SHOULD WORK, I TESTED IT OUT.

TYPE: IIf(([HANDSTACKED]=2 Or [HANDSTACKED]=3) And ([RESERVEAISLE]=13 Or
[RESERVEAISLE]=14 Or [RESERVEAISLE]=21 Or [RESERVEAISLE]=33),"HAND STACK",
"PALLET")
 
A

AccessIM

Thank you! I used your last suggestion using the IN statements because it
looked the cleanest and it worked perfectly.

Marshall Barton said:
AccessIM said:
I am trying to create a multiple IIf statement and it just isn't working.

I want to base the contents of one field ([Type]) on the contents of two
other fields ([ReserveAisle] and [HandStacked]). Here is the criteria:

If the [HandStacked] field contains a 2 or 3 and the [ReserveAisle]
contains a
13, 14, 21 or 33 I would like the [Type] field to say "Hand Stacked"
otherwise it
should say "Pallet".

Here are two IIf statements I have tried but don't work:

IIf([HandStacked]>1,IIf([HandStacked]<4,IIf([ReserveAisle]=13,IIf
([ReserveAisle]=14,IIf([ReserveAisle]=21,IIf([ReserveAisle]=33,"Hand
Stacked","Pallet"))))))

IIf([HandStacked]=2 or [HandStacked]=3 and [ReserveAisle]=13 or
[ReserveAisle]=14 or [ReserveAisle]=21 or [ReserveAisle]=33,"Hand
Stacked","Pallet")
Click to expand...


The second one is close, but you need parenthesis to specify
the order of evaluation for the Ands and Ors.

IIf( (HandStacked=2 Or HandStacked=3)
And (ReserveAisle=13 Or ReserveAisle=14 O
Or ReserveAisle=21 Or ReserveAisle=33),
"Hand Stacked","Pallet")

A slightly easier way to do this particular kind of thing
is:
IIf(HandStacked IN(2,3) And ReserveAisle IN(13,14,21,33),
"Hand Stacked","Pallet")
Click to expand...
 
R

Ron2006

WOW

In just about 5 minutes you got 5 replies .....


A lot of brilliant people with the same brilliant idea......


:)

Ron
 
C

croy

The second one is close, but you need parenthesis to specify
the order of evaluation for the Ands and Ors.

IIf( (HandStacked=2 Or HandStacked=3)
And (ReserveAisle=13 Or ReserveAisle=14 O
Or ReserveAisle=21 Or ReserveAisle=33),
"Hand Stacked","Pallet")

A slightly easier way to do this particular kind of thing
is:
IIf(HandStacked IN(2,3) And ReserveAisle IN(13,14,21,33),
"Hand Stacked","Pallet")
Click to expand...


What is "IN", in your last example? I've searched my Access
2002 help file 'till I'm blue in the face, but can't find
anything.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need some PWA help 0
Add a (sequential not autonumber) number in append query 5
Joining IIF statements together 2
IIF statement pulling from wrong field 2
IIf statement not working 4
Page numbering with Merged Document and IF statements 1
query error 12
Nesting IIf Statements in Microsoft Access 2000 4

Top