SQL in ACCESS 97

[Raptors Fan]

Hi,

Does anyone know if the SQL version used in access supports IF or CASE statements? I have seen these in T-SQL.

Examples in T-SQL:

SELECT Category = CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END
FROM titles


I have tried CASE and IF in ACCESS 97 but it can't figure out the right syntax or any info on the HELP pages.

Many thanks,

RF

p.s. I know is not a VB question, but since VB is used in Access and many use Access... maybe one of you knows

[Iouri]

I am not sure but try something like this

select (iif([Category] = 'popular_comp','Popular Computing',(iif([Category] = 'mod_cook', 'Modern Cooking','Not yet categorized')))) from Titles

Check the brackets. I might screw up


Iouri Boutchkine
iouri@hotsheet.com

[Raptors Fan]

I found something that works... ;-)
Simply, put the criteria on a table and include that table.

Here it is:

SELECT C.Name
FROM titles AS T, criteria AS C
WHERE T.Category = C.Category

Table: criteria
-----------------
Column: Category Column: Name
---------------- --------------------
popular_comp Popular Computing
mod_cook Modern Cooking
business Business
psychology Psychology
trad_cook Traditional Cooking

I think the IIF() is good for 2 options, but anything more than 2 I think this approach is more practical. However, if anyone does know if IF and CASE can be used in Access SQL... please post the syntax... thanks.