Multiple IIf Statements Not Working

[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!

 

[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!

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")

 

[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

 

[Marshall Barton]

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")

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")

 

[AccessIM]

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

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")

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")

 

[Ron2006]

WOW

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


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




Ron