Microsoft Access - Case Query

ms-access, sql

Solution

There is no CASE ... WHEN in Access SQL. You can use the Switch Function instead.

UPDATE HAI
SET REGION = Switch(
    NUMREG Like '*1', 'BDG',
    NUMREG Like '*2', 'JKT',
    NUMREG Like '*3', 'KNG'
    );

That query uses Access' default (ANSI 89 mode) * instead of % wildcard character. If you want to use the % wildcard, you can do it with the ALike comparison operator.

UPDATE HAI
SET REGION = Switch(
    NUMREG ALike '%1', 'BDG',
    NUMREG ALike '%2', 'JKT',
    NUMREG ALike '%3', 'KNG'
    );

Problem

I've just try using MS Access 2007 now I want to update a column based on other column value, in MY SQL it was successfull running this query ``` UPDATE HAI SET REGION=( CASE WHEN (NUMREG LIKE '%1') THEN 'BDG' WHEN (NUMREG LIKE '%2') THEN 'JKT' WHEN (NUMREG LIKE '%3') THEN 'KNG' END); ``` This query won't run in MS Access, there is error `Sytax error (missing operator) in query expression`. How can I solve this?

Original source