By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,500 Members | 2,520 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,500 IT Pros & Developers. It's quick & easy.

SQL Case Statement for a report

P: 17
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.
Jul 19 '07 #1
Share this Question
Share on Google+
7 Replies


JKing
Expert 100+
P: 1,206
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.
Jul 19 '07 #2

P: 17
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.
Jul 19 '07 #3

JKing
Expert 100+
P: 1,206
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.
Expand|Select|Wrap|Line Numbers
  1. Select case Number
  2.      case 1
  3.            'Return something if number is 1
  4.      case 2
  5.            'Return something if number is 2
  6. End Select
  7.  
Switch is available and is very much the same thing.
Expand|Select|Wrap|Line Numbers
  1. Switch(Number=1, "Return something if number is 1", Number=2, "Return something if number is 2")
  2.  
Jul 19 '07 #4

P: 17
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.
Jul 20 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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
Jul 20 '07 #6

P: 17
Thanks puppydogbuddy I'll give it try.
Jul 20 '07 #7

P: n/a
Good explanation puppydogbuddy.
Sep 30 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.