MS Access Query with CASE statement

问题:

I just want to get the value of one table "b" if table "a" value is "-" If the value at table "b" is empty then get the value of table "a" even if it's "-"

Microsoft Access says "Missing operator" with this query:

       SELECT   ts.data_generacio, 
        ts.estat, 
        ts.exercici, 
        Month(tsl.data) AS Mes, 
        Day(tsl.data)   AS Dia, 
        tsl.data, 
        tsl.cod_treb, 
        t.nom_treb, 
        tsl.hores, 
        p.cod_proj, 
        p.acronim       AS nom_proj,
        j.justificacio, 
        tsl.timesheet_id, 
        p.ref,
        CASE WHEN tsl.activitat != '' THEN tsl.activitat ELSE ts.activitat END AS Activitat 
FROM    timesheet_lines AS tsl 
        LEFT JOIN timesheets      AS ts 
        ON tsl.timesheet_id = ts.id 
            LEFT JOIN treballadors AS t 
            ON tsl.cod_treb = t.cod_treb 
                LEFT JOIN justificacions AS 
                ON ts.id_justificacio = j.id 
                    LEFT JOIN projectes AS p 
                    ON j.cod_proj = p.cod_proj;

I think the error is on the CASE expression line.

回答1:

MS Access does not support CASE statements. Use IIF:

IIF(tsl.activitat <> '', tsl.activitat, ts.activitat ) AS Activitat

I'm not sure if Access supports aliases on LEFT JOIN either, but it probably does

Note (although correctly tagged), the title of your question might be troublesome... most people refer to Transact-SQL (TSQL) when writing "MS SQL". Transact-SQL is used by MS SQL Server, but not by MS Access, which uses its own SQL dialect (called "Access SQL", and pretty limited in comparison)