Stefan Stefan - 2 months ago 6
SQL Question

Syntax error in MS Access Query

I'm getting a syntax error ("Missing Operator") in my MS Access record source.
SQL Server 2012 executes it without a problem but when I let Access 2016 execute it, I get this error. I expect that Access has different case statement requirements but I'm not sure.

SELECT
r.Rubrieknaam,
CASE
WHEN (SELECT count(Voorwerpnummer)
FROM VoorwerpInRubriek
WHERE rubrieknummer = r.Rubrieknummer) IS NULL
THEN 0
ELSE (SELECT count(Voorwerpnummer)
FROM VoorwerpInRubriek
WHERE rubrieknummer = r.Rubrieknummer)
END AS [nInRubriek],
CASE
WHEN (SELECT Count(*)
FROM Rubriek
WHERE HoofdrubriekNr = r.rubriekNummer) IS NULL
THEN 0
ELSE (SELECT Count(*)
FROM Rubriek
WHERE HoofdrubriekNr = r.rubriekNummer)
END AS [nSubrubrieken],
CASE
WHEN (SELECT Rubrieknaam
FROM Rubriek
WHERE Rubrieknummer = r.hoofdrubrieknr) IS NULL
THEN 'N.V.T.'
ELSE (SELECT Rubrieknaam
FROM Rubriek
WHERE Rubrieknummer = r.hoofdrubrieknr)
END AS [Hoofdrubrieknaam]
FROM
Rubriek r;


Does anybody know what the problem is?

Thanks in advance.

Answer

Yes. CASE-ELSE is T-SQL.

Use IIf(expression, true expression, false expression) in Access SQL.

Comments