Does MS Access support CASE WHEN clause if connect with ODBC

Question

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  gt  10  THEN 1 ELSE 0 END  FROM demo   Microsoft  ODBC Microsoft Access Driver  Syntax error  missing operator  in query expression   CASE WHEN  AGE  gt  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  gt  10 FROM demo  but in others it have to be   SELECT  CASE WHEN  AGE  gt  10  THEN 1 ELSE 0 END  FROM demo

User · Accepted Answer

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

User · Answer

Since you are using Access to compose the query  you have to stick to Access s version of SQL   To choose between several different return values  use the switch   function  So to translate and extend your example a bit   select switch    age  gt  40  4    age  gt  25  3    age  gt  20  2    age  gt  10  1    true  0   from demo   The  true  case is the default one  If you don t have it and none of the other cases match  the function will return null   The Office website has documentation on this but their example syntax is VBA and it s also wrong  I ve given them feedback on this but you should be fine following the above example

User · Answer

I have had to use a multiple IIF statement to create a similar result in ACCESS SQL    IIf  refi type  Like  FHA ST    F  IIf  refi type   VA IRRL   V      All remaining will stay Null

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

Examples related to sql

Examples related to database

Examples related to ms-access

Examples related to odbc