Inputting case-based conditional statement with wildcard into SQL

[anthonyd]

As a disclaimer, I'm not sure if what I'm specifically trying to do can in
fact be done, but I figured that the program and users here are far smarter
than me.

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. I have
inputted the following:

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

I'm sure there are numerous syntax errors here. Any help would be greatly
appreciated!

 

[Sylvain Lafontaine]

Access query? Access doesn't support the keyword CASE, you have to use the
function IIF(....) instead. You should ask this question in a newsgroup
about the version of SQL supported by Access, such as m.p.access.queries.

This newsgroup is about ADP and SQL-Server and the answer that you will get
will be for T-SQL and not for Access.

 

[DAVID]

Access query? Access doesn't support the keyword CASE, you have to use the
function IIF(....) instead. You should ask this question in a newsgroup
about the version of SQL supported by Access, such as m.p.access.queries.

This newsgroup is about ADP and SQL-Server and the answer that you will get
will be for T-SQL and not for Access.

good answer except for this:

Access doesn't support the keyword CASE, you have to
use the you have to use the function IIF(....) instead.

access inline SQL: SWITCH, CHOOSE, IIF
access query embedded VBA: IIF,SWITCH,CHOOSE,IF,CASE, etc

This newsgroup is about ADP and SQL-Server and
the answer that you will get will be for T-SQL
and not for Access.

best to just leave it at that when people ask.

 

[Sylvain Lafontaine]

good answer except for this:



access inline SQL: SWITCH, CHOOSE, IIF
access query embedded VBA: IIF,SWITCH,CHOOSE,IF,CASE, etc


best to just leave it at that when people ask.

Best to indicate to people that they are either in the wrong newsgroup or
that they are in the right newsgroup but that they have posted conflictual
informations instead of leaving them in the dark, waiting for an answer that
will never come.