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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation