Mulitple IIf statements - Too many

[Guest]

How many nested IIf statements are allowed in a single query field?

I have a list of 41 slot ranges with corresponding section numbers. I am
trying to use a query to say if the slot number for an item is between the
two slot range numbers, assign this section number. See my example below.

IIf([Slot] Between "030101" And "030192","401", IIf([Slot] Between "030701"
And "031192","402", IIf([Slot] Between "031301" And "031792","403",

I did this 41 times for each section with a FALSE value at the end and, when
I try to run the query, it says the query is too complex.

Does anyone have a better way to do this or any suggestions? Thank you so
much!

 

[Guest]

I don't know if there is a maximum number of nested IIf statements, but you
are either hitting that or some other limiation because of the length of the
statement. A better approach for something this complex would be to use a
function. Define the function as a Public Function in a standard module and
call it from your query.

Public Function SlotValue(strSlot As String) As String

Select Case strSlot
Case "030101" To "030192"
SlotValue = "401"
Case "030701" To "031192"
SlotValue = "402"
Case "031301" To "031792"
SlotValue = "402"
End Select
End Function

 

[Guest]

I created the module but, forgive me, I do not know how to call it from a
query. Could you please explain that piece of your response?

Thank you so much for the quick response. You all are so helpful!

I don't know if there is a maximum number of nested IIf statements, but you
are either hitting that or some other limiation because of the length of the
statement. A better approach for something this complex would be to use a
function. Define the function as a Public Function in a standard module and
call it from your query.

Public Function SlotValue(strSlot As String) As String

Select Case strSlot
Case "030101" To "030192"
SlotValue = "401"
Case "030701" To "031192"
SlotValue = "402"
Case "031301" To "031792"
SlotValue = "402"
End Select
End Function

How many nested IIf statements are allowed in a single query field?

I have a list of 41 slot ranges with corresponding section numbers. I am
trying to use a query to say if the slot number for an item is between the
two slot range numbers, assign this section number. See my example below.

IIf([Slot] Between "030101" And "030192","401", IIf([Slot] Between "030701"
And "031192","402", IIf([Slot] Between "031301" And "031792","403",

I did this 41 times for each section with a FALSE value at the end and, when
I try to run the query, it says the query is too complex.

Does anyone have a better way to do this or any suggestions? Thank you so
much!

 

[Guest]

You need to create a Calculated Field for your query, and call it from there.
The code below should go in the Field row in the query builder where you
want the value to appear:

SlotCode: SlotValue([slot])

It would be the same as using the IIf.s

SlotCode: IIf(.....


Sorry, I should have included that in my original response.
Therefore, there will be no charge for this service

I created the module but, forgive me, I do not know how to call it from a
query. Could you please explain that piece of your response?

Thank you so much for the quick response. You all are so helpful!

I don't know if there is a maximum number of nested IIf statements, but you
are either hitting that or some other limiation because of the length of the
statement. A better approach for something this complex would be to use a
function. Define the function as a Public Function in a standard module and
call it from your query.

Public Function SlotValue(strSlot As String) As String

Select Case strSlot
Case "030101" To "030192"
SlotValue = "401"
Case "030701" To "031192"
SlotValue = "402"
Case "031301" To "031792"
SlotValue = "402"
End Select
End Function

How many nested IIf statements are allowed in a single query field?

I have a list of 41 slot ranges with corresponding section numbers. I am
trying to use a query to say if the slot number for an item is between the
two slot range numbers, assign this section number. See my example below.

IIf([Slot] Between "030101" And "030192","401", IIf([Slot] Between "030701"
And "031192","402", IIf([Slot] Between "031301" And "031792","403",

I did this 41 times for each section with a FALSE value at the end and, when
I try to run the query, it says the query is too complex.

Does anyone have a better way to do this or any suggestions? Thank you so
much!

 

[John Vinson]

How many nested IIf statements are allowed in a single query field?

I have a list of 41 slot ranges with corresponding section numbers. I am
trying to use a query to say if the slot number for an item is between the
two slot range numbers, assign this section number. See my example below.

IIf([Slot] Between "030101" And "030192","401", IIf([Slot] Between "030701"
And "031192","402", IIf([Slot] Between "031301" And "031792","403",

I did this 41 times for each section with a FALSE value at the end and, when
I try to run the query, it says the query is too complex.

Does anyone have a better way to do this or any suggestions? Thank you so
much!

Klatuu's suggestion is an excellent one and will work, but there are a
couple of alternatives that you might want to consider.

For quick & dirty cases where there aren't *too* many alternatives,
the Switch() function is more compact and efficient than multiple
IIF's. It takes any number of arguments in pairs; the pairs are read
left to right, and when the function first finds that the first member
of the pair is TRUE it returns the second member of the pair and
quits:

Switch([Slot] BETWEEN "030101" And "030192","401",
[Slot] Between "030701" And "031192","402",
[Slot] Between "031301" And "031792","403",
....,
TRUE, "999")

Even better would be a table-driven solution. The IIF, Switch, and
even Klatuu's VBA code require you to dig into complex expressions if
the ranges or the returned value ever change. Instead, you can create
a Table named Sections:

Sections
SlotLow
SlotHigh
Section

with the obvious values; you can create a Query joining your table to
Sections using SQL like

SELECT mytable.thisfield, mytable.thatfield, Sections.Section\
FROM mytable
INNER JOIN Sections
ON mytable.[Slot] >= [Sections].[SlotLow]
AND mytable.[Slot] <= [Sections].[SlotHigh];


John W. Vinson[MVP]

 

[Guest]

The database I was asking about originally has been put on hold. I tried to
use the SWITCH statement in a different database and I am getting an "#Error"
in the field. I want to have customer numbers in one range return one value
and customer in a second range return another value. If the customer number
does not fall within either of these two ranges, it should return the value
from the Division field in the table. Here is my statement. Can you see
what is wrong with it? Thank you.

RptDivision: Switch([CustomerNo] Between "159001" And "159699","ABS
Pharmacy",[CustomerNo] Between "156300" And "156999","SV
Pharmacy",True,[Division])

How many nested IIf statements are allowed in a single query field?

I have a list of 41 slot ranges with corresponding section numbers. I am
trying to use a query to say if the slot number for an item is between the
two slot range numbers, assign this section number. See my example below.

IIf([Slot] Between "030101" And "030192","401", IIf([Slot] Between "030701"
And "031192","402", IIf([Slot] Between "031301" And "031792","403",

I did this 41 times for each section with a FALSE value at the end and, when
I try to run the query, it says the query is too complex.

Does anyone have a better way to do this or any suggestions? Thank you so
much!

Klatuu's suggestion is an excellent one and will work, but there are a
couple of alternatives that you might want to consider.

For quick & dirty cases where there aren't *too* many alternatives,
the Switch() function is more compact and efficient than multiple
IIF's. It takes any number of arguments in pairs; the pairs are read
left to right, and when the function first finds that the first member
of the pair is TRUE it returns the second member of the pair and
quits:

Switch([Slot] BETWEEN "030101" And "030192","401",
[Slot] Between "030701" And "031192","402",
[Slot] Between "031301" And "031792","403",
....,
TRUE, "999")

Even better would be a table-driven solution. The IIF, Switch, and
even Klatuu's VBA code require you to dig into complex expressions if
the ranges or the returned value ever change. Instead, you can create
a Table named Sections:

Sections
SlotLow
SlotHigh
Section

with the obvious values; you can create a Query joining your table to
Sections using SQL like

SELECT mytable.thisfield, mytable.thatfield, Sections.Section\
FROM mytable
INNER JOIN Sections
ON mytable.[Slot] >= [Sections].[SlotLow]
AND mytable.[Slot] <= [Sections].[SlotHigh];


John W. Vinson[MVP]