Does MS Access support "CASE WHEN" clause if connect with ODBC?

database, ms-access, odbc, sql

Solution

You could use `IIF` statement like in the next example:

SELECT
   IIF(test_expression, value_if_true, value_if_false) AS FIELD_NAME
FROM
   TABLE_NAME

Problem

Does ODBC support `CASE WHEN` clause for MS Access? Is there any other database which does not support the `CASE WHEN` clause? I tried the following query while connecting to MS Access with ODBC but get an exception. `SELECT (CASE WHEN (AGE > 10) THEN 1 ELSE 0 END) FROM demo` `[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(CASE WHEN (AGE > 10) THEN 1 ELSE 0 END)'` I'm try to find a common way which works for most of the database to generate (compute) the new 'boolean columns' with an comparison expression while connect with ODBC. Actually, MS Access support the comparison in SELECT clause, but for some other databases CASE clause are needed. For MS Access, the SQL can be `SELECT AGE > 10 FROM demo` but in others it have to be `SELECT (CASE WHEN (AGE > 10) THEN 1 ELSE 0 END) FROM demo`

Original source