Syntax error in MS Access Query

Question

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.

MS Access does not support `case`. In addition, `count()` in a subquery doesn't return `NULL`, it returns `0`. — Gordon Linoff, May 27 '16 at 11:31

score 2 · Accepted Answer · edited May 27 '16 at 11:40

2

Yes. CASE-ELSE is T-SQL.

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

edited May 27 '16 at 11:40

Maciej Los

8,468
1
20
35

answered May 27 '16 at 11:28

Gustav

53,498
7
29
55

Thank you this solved the problem. It however massively affected the query performance so I decided to not use it in my form. This was the answer for my question I was looking for though. – Stefan May 27 '16 at 12:07

score 1 · Answer 2 · answered May 27 '16 at 11:43

1

The query should look like:

SELECT r.Rubrieknaam, 
       (SELECT count(Voorwerpnummer) FROM VoorwerpInRubriek WHERE rubrieknummer = r.Rubrieknummer
       ) AS [nInRubriek], 
       (SELECT Count(*) FROM Rubriek WHERE HoofdrubriekNr = r.rubriekNummer
       )  AS [nSubrubrieken], 
       NZ(Rubrieknaam, "N.V.T.") AS [Hoofdrubrieknaam]
FROM Rubriek r;

Conditional logic isn't even needed.

answered May 27 '16 at 11:43

Gordon Linoff

1,242,037
58
646
786

Yes I found that out and returned my query to the original query(which is like the one you send.) I got an error about invalid numeric values making me think that the 0 or NULL created a problem. The problem however was that the connection to my database was giving errors and returning invalid values. Thanks for the help. – Stefan May 27 '16 at 12:09

Syntax error in MS Access Query

2 Answers2