SQL Case Statement for a report

[atrottier]

Is it possible within Access 2003 to use a Case statement in the query sql that returns info for a report. I mostly work with a Sybase database system which allows such commands. Can't find any references as to whether this is legal in Access. If so, could you possibly include a sample of its usage(syntax) or a place to go and find an example. Thanks for any and all replies.

[JKing]

Hi, here's a link that explains the switch function. It may be what you are looking for.

Switch Function

Along the right hand side of webpage there is a "See Also" section containing Choose and Iif. You may want to take a look at both of those as well.

[atrottier]

Thanks for the reply but thats not what I'am looking for. I know you can code a Case statement in VBA script (say to use in the click event of a button) but I was wondering if you can use a Case statement within a sql query script for a report.

[JKing]

What is it youre trying to accomplish within the query? Could you perhaps give an example of the data you are trying to manipulate and the outcome you wish to acheive?

This type of structure isn't available in SQL.
[code=vb]
Select case Number
case 1
'Return something if number is 1
case 2
'Return something if number is 2
End Select
[/code]

Switch is available and is very much the same thing.
[code=sql]
Switch(Number=1 , "Return something if number is 1", Number=2, "Return something if number is 2")
[/code]

[atrottier]

JKing, I have there different reports to create. The reports are based on dates ranging from todays date to one week, two weeks or 4 weeks out depending on which one the user selects. There is two columns (Actual Start and Forecast Start) on the table in which to compare these dates. The first column "Actual Start" is a text field which can have a date type format (ie "07/20/2006" or an entry of "N/A" which is why the column is a text field. The second column "Forecast Start" is a date/time field. The first column to compare too when this report is run is the "Actual Start" column. If IsDate(Actual Start) than compare to find which rows fall between todays date and the selected option of either 1,2 or 4 weeks out. If the "Actual Start" value is "N/A" then use the "Forecast Start" column value to retrieve the qualifying rows.
I was thinking of creating another table that would be used to hold values that are found to qualify through a series of If/Then/Else statements with embedded sql statements in the command button "Clicked" event and then just retrieve the report from this new table. This would be a lot of extra code so it would be cleaner if I could do it right in the sql retrieval statement for the report, hence the question of whether a "CASE" statement was possible. Thanks again.

[puppydogbuddy]

As Jared told you, the Switch Function is the MS Access equivalent of the Case statement. Perhaps if you look at the examples below, comparing the Case statement syntax in MS SQL Server with the Switch function syntax used in MS Access you will relate to it better:

SQL Server Syntax

SELECT 'Price Category' = CASE
WHEN price IS NULL THEN 'Not yet priced'
WHEN price < 10 THEN 'Very Reasonable Title'
WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
ELSE 'Expensive book!'
END
FROM titles
ORDER BY price

_______________ _______________ __

MS Access Equivalent Using the Switch Function

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

[atrottier]

Thanks puppydogbuddy I'll give it try.

[SQL Apprentice]

Good explanation puppydogbuddy.