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

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.