Ask the Expert

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 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,"


    Requires Free Membership to View

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.

This was first published in April 2001

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: