What's The Syntax For A CASE Statement In SQL (MS-Access)?

Question

I am trying to write a CASE statement in MS-Access and it is not working. Please give me the syntax for this.

I have searched, edited and tried out examples from the internet but they don't work. Please give me a simple example with just 2 cases so that I can follow it easily.

If you actually test your code on MS-Access before posting it here, please let me know that too. I appreciate your help very much.

Answer 1

Try here :

http://www.firebirdsql.org/manual/migration-mssql-syntax.html

or

http://msdn2.microsoft.com/en-us/library/aa140011(office.10).aspx

hope this helps!

Answer 2

Sql Case Syntax

Answer 3

Access Case Statement

Answer 4

Example 1:
Assume myChoice is an integer variable and you are performing different actions if the value is 1, 2, or 3.

Select Case myChoice
Case 1
'your code here
Case 2
'more code here
Case 3
'and more code
Case Else
'code for a value outside 1, 2, or 3
End Select

Watch the data type, for example, if your variable is a string, it would look something like:

Select Case myStringChoice
Case "red"
'code
Case "blue"
'code
End Select

Hope this is what you're looking for!

Answer 5

Try this

SELECT iif(price IS NULL,"Not yet priced",iif(price < 10,"Very Reasonable Title",iif(price >= 10 and price < 20,
"Coffee Table Title","Expensive book!")))
as Price_Category
FROM titles
ORDER BY price



or use the switch function instead


SELECT Switch(
price IS NULL,"Not yet priced"
,price < 10,"Very Reasonable Title"
,price >= 10 and price < 20, "Coffee Table Title"
,True, "Expensive book!"
)
as Price_Category
FROM titles
ORDER BY price