select case statement in sql

[mg]

using access-2007
i have been trying to use the SELECT CASE statement, as given in many online
references, but keep getting a syntax error (missing operator).....

SELECT Vendor.name,
CASE Vendor.[name]
WHEN 'A&A Ventures'
THEN 'true'
ELSE 'false'
END AS appliances
FROM Vendor;

i couldnt use the IF statement, so i found lots of references to using
'case', but i cant make it work.

 

[Michel Walsh]

SELECT [name],
SWITCH( [name]="A&A Ventures", true, true, false) AS appliances
FROM Vendor


SWITCH arguments work by pair, and looks more like the second syntax of
SQL-CASE statement.

If you have just one test, you can use iif:


SELECT [name],
iif([name]="A&A Ventures", true, false) AS appliances
FROM Vendor



and since, with Jet, there is a BOOLEAN data type, you can simply use:


SELECT [name],
[name]="A&A Ventures" AS appliances
FROM Vendor




where you note that = means a test, rather than the use of an alias (as with
MS SQL Server):


SELECT [name],
Appliances = CASE Vendor.[name]
WHEN 'A&A Ventures'
THEN 'true'
ELSE 'false'
END
FROM vendor -- MS SQL Server Syntax




Vanderghast, Access MVP

 

[mg]

thanks, the switch thing works....

SELECT [name],
SWITCH( [name]="A&A Ventures", true, true, false) AS appliances
FROM Vendor


SWITCH arguments work by pair, and looks more like the second syntax of
SQL-CASE statement.

If you have just one test, you can use iif:


SELECT [name],
iif([name]="A&A Ventures", true, false) AS appliances
FROM Vendor



and since, with Jet, there is a BOOLEAN data type, you can simply use:


SELECT [name],
[name]="A&A Ventures" AS appliances
FROM Vendor




where you note that = means a test, rather than the use of an alias (as
with MS SQL Server):


SELECT [name],
Appliances = CASE Vendor.[name]
WHEN 'A&A Ventures'
THEN 'true'
ELSE 'false'
END
FROM vendor -- MS SQL Server Syntax




Vanderghast, Access MVP

using access-2007
i have been trying to use the SELECT CASE statement, as given in many
online references, but keep getting a syntax error (missing
operator).....

SELECT Vendor.name,
CASE Vendor.[name]
WHEN 'A&A Ventures'
THEN 'true'
ELSE 'false'
END AS appliances
FROM Vendor;

i couldnt use the IF statement, so i found lots of references to using
'case', but i cant make it work.