COUNT(CASE WHEN...

[Mr. Bungle]

Hello,

Do Access queries support the CASE WHEN SQL command? Whenever I try to
use them, I get a syntax error (missing operator) and the word WHEN is
highlighted.

For Example:

SELECT
COUNT(CASE WHEN MyField <20 THEN 1 ELSE NULL END)
FROM MyTable;

FYI, the WHEN and MyField can be reversed and still be correct SQL
Syntax:

SELECT
COUNT(CASE MyField WHEN <20 THEN 1 ELSE NULL END)
FROM MyTable;
Both methods work fine in SQL Query Analyzer but not in Access.
Thanks in advance for your comments.

Jeremy

[Rick Brandt]

"Mr. Bungle" <sl********@hotmail.com> wrote in message
news:ab*************************@posting.google.co m...

Hello,

Do Access queries support the CASE WHEN SQL command? Whenever I try to
use them, I get a syntax error (missing operator) and the word WHEN is
highlighted.

For Example:

SELECT
COUNT(CASE WHEN MyField <20 THEN 1 ELSE NULL END)
FROM MyTable;

FYI, the WHEN and MyField can be reversed and still be correct SQL
Syntax:

SELECT
COUNT(CASE MyField WHEN <20 THEN 1 ELSE NULL END)
FROM MyTable;
Both methods work fine in SQL Query Analyzer but not in Access.
Thanks in advance for your comments.

CASE is not supported in Access/Jet. Check the help files for functions IIf() ,
Switch(), Choose(). They can usually provide similar functionality

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com