Value Substitution using a CASE statement

[gixer]

Help!

I'm trying to run the following query in MS Access SQL, which I wrote in MS SQL Sever Transact Query Analyzer.

select CASE qd.idiscounttypeid
when 104193 then 'Yes'
when 102441 then 'No'
END as 'Selected', qd.tirecordstatus
from quotedetail qd

It works fine in Query Analyzer, but it doesn't work in Access.

Any help is appreciated!

 

[swampBoogie]

Access does not support case expressions (nor case statements)

select iif(qd.idiscounttypeid=104193,'Yes'
iif(qd.idiscounttypeid=102441,'No',''))
as Selected, qd.tirecordstatus
from quotedetail qd

 

[gixer]

Thanks for the help. What if I want to use more then 2 expressions.
eg.
idiscounttypeid=104193,'Yes'
idiscounttypeid=102441,'No'
idiscounttypeid=102442,'Half'
idiscounttypeid=102443,'Full'

 

[cmmrfrds]

You are not limited to 2 conditions with the iif function. Another option is the Switch function, which you can look up in Access help for an example.

 

[gixer]

Thanks cmmrfrds.

I still can't get the iff fuction to work. It tells me the expression is too complex.

How do you use the Switch function in a select statement. Sorry, I don't know much about access programming.

 

[cmmrfrds]

This is out of the help file. the structure.
Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

the example.
Matchup = Switch(CityName = "London", "English", CityName = "Rome", "Italian", CityName = "Paris", "French&quot

your data. Just follow the example.

select switch(qd.idiscounttypeid=104193,"Yes",
qd.idiscounttypeid=102441,"No&quot
as Selected, qd.tirecordstatus
from quotedetail qd

 

[gixer]

Cool,

Thanks cmmrfrds!