Re: How to use "IIf" when there are multiple cases
- From: "David Cox" <nospam@xxxxxxxxxx>
- Date: Tue, 10 Jul 2007 06:21:37 GMT
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
"anthonyd" <anthonyd@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E0F555CE-AC18-4EEE-AA29-ABCA0098CFAB@xxxxxxxxxxxxxxxx
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!
.
- Prev by Date: Re: Problem integrating tables into query
- Next by Date: Displaying Zeros in Crosstab Queries
- Previous by thread: Re: How to use "IIf" when there are multiple cases
- Next by thread: Displaying Zeros in Crosstab Queries
- Index(es):
Relevant Pages
|
