Re: Mulitple IIf statements - Too many
- From: AccessIM <AccessIM@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 26 Jan 2007 13:00:01 -0800
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])
"John Vinson" wrote:
On Wed, 22 Nov 2006 10:09:02 -0800, AccessIM.
<AccessIM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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]
- Prev by Date: Re: Delete matching records from two tables
- Next by Date: Re: expression question
- Previous by thread: Re: Consecutive months
- Next by thread: Simple concatenation of fields
- Index(es):
Relevant Pages
|
