Select case instruction in access query

  • Thread starter Thread starter Herbert Saal
  • Start date Start date
H

Herbert Saal

Is there an instruction in access similar to the CASE Instruction in Transac
SQL?

Ex:
USE pubs
GO
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,
CAST(title AS varchar(25)) AS 'Shortened Title',
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO
Thanks in advance,Herbert Saal
 
You could use:
- IIf()
- Switch()
- Choose()
- create a user defined function
- preferred method is to use a lookup table so you don't have humugous
expressions that should be data in tables
 
You could have a look at the Switch function. Your example would look
something like
SELECT Switch(type = 'popular_comp' , 'Popular Computing' , type =
'mod_cook' , 'Modern Cooking' , type = 'business' , 'Business' , type =
'psychology' , 'Psychology' , type = 'trad_cook' , 'Traditional Cooking' ,
True, 'Not yet categorized') As category etc

Hope This Helps
Gerald Stanley MCSD
 
Back
Top