Syntax Error (missing operator) in the query expression

Question

I wrote a query in SQL and want to use it in Microsoft Access 2010. I made most of the necessary translations, but it seems like I missed one. However, I cannot find what the error is. Can anyone find the error?

SELECT dbo_job.item, sub2.item AS NewCheck
FROM dbo_job 
LEFT JOIN (
    SELECT dbo_job.item
    FROM dbo_job 
    WHERE dbo_job.job_date<=#7/13/2015# AND dbo_job.job_date>=#6/22/2015#
    GROUP BY dbo_job.item) 
AS sub ON sub.item=dbo_job.item
LEFT JOIN (
    SELECT dbo_job.item
    FROM dbo_job
    WHERE dbo_job.job_date<#7/13/2015#
    GROUP BY dbo_job.item)
AS sub2 ON sub2.item=dbo_job.item
WHERE dbo_job.job_date=#7/20/2015# AND sub.item IS NULL
GROUP BY dbo_job.item, sub2.item

you use `GROUP BY` but are not using any aggregate functions. — Vamsi Prabhala, Jul 15 '15 at 17:13
i believe you need your joins in parenthesis ...FROM ((dbo_job LEFT JOIN ...ON sub.item=dbo_job.item) ... ON sub3.item=dbo_job.item) — Suing, Jul 15 '15 at 17:19

score 0 · Accepted Answer · answered Jul 15 '15 at 17:41

MS Access requires additional parentheses around joins, so this might work:

SELECT dbo_job.item, sub2.item AS NewCheck
FROM (dbo_job LEFT JOIN
      (SELECT dbo_job.item
       FROM dbo_job 
       WHERE dbo_job.job_date<=#7/13/2015# AND dbo_job.job_date>=#6/22/2015#
       GROUP BY dbo_job.item
      ) AS sub
      ON sub.item = dbo_job.item
     ) LEFT JOIN
     (SELECT dbo_job.item
      FROM dbo_job
      WHERE dbo_job.job_date<#7/13/2015#
      GROUP BY dbo_job.item
     ) AS sub2
     ON sub2.item = dbo_job.item
WHERE dbo_job.job_date = #7/20/2015# AND sub.item IS NULL
GROUP BY dbo_job.item, sub2.item;

Syntax Error (missing operator) in the query expression

1 Answers1