How to use "IIf" when there are multiple cases

[anthonyd]

Does the "IIf" command work if I have a number of different cases, each of
which need to call out a different result? If so, what is the syntax for it?
Is there a limit to the number of cases I might be able to have?

I am trying to enter a new column in a query called "Zone" that will
conditionally input a number based on the last 3 digits of a column called
"Zip code" in my Access query. For example, I'm trying to get the number 4 to
show up in "Zone" for all zip codes with the last 3 digits of 010-011.
Before I realized that ACCESS didn't support case-based conditions, I had
inputted the following:

Zone = CASE zip code
WHEN zip code Between LIKE *010 and LIKE *011 THEN 4
ELSE 3
END,
FROM Zone;

If ACCESS lacks this capability, is there anything else I could do? I was
considering doing an inner join, but wasn't sure how to get the query to
match just the last 3 digits of the zip code.

Any help would be greatly appreciated!

 

[KARL DEWEY]

I use a 'translation table' when I need to convert many items. The
translation table would have at least two fields - old and new. In the
design view of the query place both your data table and the translation table
but do not join. Pull down the 'old' and 'new' fields to the field row of
the grid.
OLD New
010 3
011 3
012 4
013 4
Under the OLD field as criteria put Right([zip code],3) to make it work.

 

[DAVID]

Using a table is a much better idea than using

SWITCH
or
CHOOSE

or global VBA functions with CASE.

Before I realized that ACCESS didn't support
case-based conditions, I had

(david)

 

[David Cox]

Another way to tackle this:

Your input is Right(zipcode,3)

Look for those chracters in a string:
"011 012 013 014 015 " ... etc
eg instr("011 012 013", right(zipcode,3))
derive an index from that by adding 3 and dividing by 4

use that index to find the resulting value using mid
someting like (unchecked):

mid("4433",instr("010 011 012 013", right(zipcode,3))+3)\4,1)

the \ is integer divide, / probably works.

HTH