Rewriting a SQL Server select statement to work with Access
Below is part of a select statement that was written for SQL Server. Can you help me rewrite this select statement...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
to work with an Access Database? I'm getting a syntax error:
strSQL = "SELECT SurveyName AS [Survey],SurveyAdministrator AS [Administrator]," strSQL = strSQL & "Anonymous = CASE isAnon WHEN 0 THEN 'No' CASE ELSE 'Yes' END," strSQL = strSQL & "[Email Notification] = CASE isEmail WHEN 0 THEN 'No' CASE ELSE 'Yes' END,"
First, let's rewrite the query without the progamming language stuff, and indent it to make it easier to scan --
SELECT SurveyName AS [Survey] , SurveyAdministrator AS [Administrator] , Anonymous = CASE isAnon WHEN 0 THEN 'No' CASE ELSE 'Yes' END , [Email Notification] = CASE isEmail WHEN 0 THEN 'No' CASE ELSE 'Yes' END
This query illustrates both methods of specifying a column alias. The "as" method is the standard, and the "=" method is an equivalent alternative in SQL/Server. Microsoft Access uses the "as" method.
It looks like the CASE structure in the example above is incorrect (there should be no CASE in front of the ELSE), but this part has to be rewritten anyway, because there's no CASE structure in Access.
Here's the equivalent query in Access syntax --
SELECT SurveyName AS Survey , SurveyAdministrator AS Administrator , Iif(isAnon=0,'No','Yes') AS Anonymous , Iif(isEmail=0,'No','Yes') AS [Email Notification]
The Iif function in Access takes three parameters -- a boolean expression to be evaluated, the result to return if the test is true, and the result if the test is false.
Note you only need the square brackets in Access when the column or column alias contains a blank or other special character.
By the way, Iifs can be nested to handle more complex conditions. For example,
SELECT Iif(Isnull(Mark),'?', Iif(Mark>=80,'A', Iif(Mark>=70,'B', Iif(Mark>=60,'C', Iif(Mark>=50,'D','F'))))) AS Grade
For news, advice and other information about SQL server development, click here.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments