SELECT CASE 3

[Katya85S]

Guys, do we have SELECT CASE fiture in MS Access Query?
if so, does the syntax differ from MS SQL syntax?
I'm trying to implement teh query:
SELECT Category,
Reason = case Reason when 1 then "Reason 1" when 2 then "Reason 2" end
FROM tblCategory
I'm getting an error:
"Syntax error (missing operator) in query expression "Reason = case Reason when 1 then "Reason 1" when 2 then "Reason 2" end"
What do i do wrong?
Thank you all in advance.

 

[lespaul]

iif(Reason = 1, "Reason 1", iif(Reason = 2, "Reason 2", "Something Else"))

you could also look into the switch function if you are using Access2000 or higher.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[sxschech]

In a query you probably need to use IIf instead of Case.

SELECT Category,
IIf(Reason = 1,"Reason 1","Reason 2") AS Reason
FROM tblCategory

For multiple statements...


SELECT Category,
IIf(Reason = 1,"Reason 1", IIf(Reason = 2,"Reason 2", IIf(Reason = 3,"Reason 3", IIf(Reason = 4,"Reason 4", )))) AS Reason
FROM tblCategory

 

[Katya85S]

Many thanks to both of you. Now it works
Thak you guys!

 

[Eupher]

No disrespect to the good answers posted so far, but in cases like this I prefer the Choose function over multiple nested IIf's, like so:

Choose([Reason], "Reason 1", "Reason 2", "Reason 3", "Reason 4") As Reason

HTH,

Ken S.

 

[Katya85S]

Thank you Eupher!
I do use indexes for the Reason field (I've checked MS Access help for the Choose function, i havent' heard about it before), and CHOOSE would be a proper function to use in my case. Thank you very much for your advice.
Guys, you all've been very helpful.
Many thanks

 

[PHV]

As I believe it's simpler to maintain data than complex formulas embedded in SQL code, I'd suggest a lookup table for the reasons ...

 

[Katya85S]

so you are saying an extra join in a query has less price for the query then one of the functions. Right?
I just want to know an expert opinion...

 

[gmmastros]

Forget about performance for a moment because having a lookup table with 4 records will always be super fast. The bigger issue is software maintenance. Supose you hardcode your list (with nested iif's or choose, or whatever). Then, a year later, the boss says... we need to add another Reason. If you hard code your list, then you are forced to go to all the queries that use it, and make the change. If you use a look up table, all you need to do is to add a record in that table.

Bottom line, a look up table is less costly to maintain and usually does not have any performance implications.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[PHV]

The main two reasons I see:
1) less maintenance effort
2) simpler upgrade to another RDBMS

 

[Katya85S]

George and PHV, thank you for explanations.
Your points make a total sense.
I'm using your suggestion.
Thank you much guys!