CASE function doesn't work in Access query

Question

I'm putting this SQL code in an MS Access query, but it doesn't work. "Syntax error (no operator) in the query expression." What's wrong with this SQL code?

SELECT URL_ID
CASE
WHEN URL_ID LIKE '*SEM*' THEN 'Google'
WHEN URL_ID LIKE '*DIR*' THEN 'Bezpośredni'
ELSE 'Others'
END
FROM URL;

Possible duplicate of [Does MS Access support "CASE WHEN" clause if connect with ODBC?](https://stackoverflow.com/questions/14920116/does-ms-access-support-case-when-clause-if-connect-with-odbc) — PM 77-1, Oct 31 '18 at 17:07

Yogesh Sharma · Answer 1 · 2018-10-31T17:11:13.793

0

MS Access doesn't support CASE expression use IIF() OR SWITCH() instead :

SELECT URL_ID,
       IIF(URL_ID LIKE '*SEM*', 'Google', 
                  IIF(URL_ID LIKE '*DIR*', 'Bezpośredni', 'Others'
                     )                     
          ) 
FROM URL;

edited Oct 31 '18 at 17:11

answered Oct 31 '18 at 17:06

Yogesh Sharma

49,870
5
26
52

Why not `switch`? – PM 77-1 Oct 31 '18 at 17:09
Thanks! It works now! So MS Access doesn't support CASE expression? – matjan2 Oct 31 '18 at 17:10
@Yogesh Sharma, do IFF or SWITCH will work in mysql? – matjan2 Oct 31 '18 at 17:27
@matjan2. . . No, In `MySQL` you can use `IF()` or `CASE` expression instead. – Yogesh Sharma Oct 31 '18 at 17:28
@YogeshSharma could you recommend me some learing SQL materials and materials where difference between SQL in Access and MySQL are showed? – matjan2 Oct 31 '18 at 17:50

CASE function doesn't work in Access query

1 Answers1