Access 2010: Syntax Error (Missing Operator) in Query Expression

Access 2010: Syntax error (missing operator) in query expression

In Access you need parentheses when you have more than one join:

FROM (drugs as a 
  INNER JOIN warehouse as b
    ON a.ID = b.drug_id) 
  INNER JOIN pharmacy as c 
    ON b.drug_id = c.drug_id

Syntax error (missing operator) in query expression 'EXTRACT (YEAR FROM Starting_Date)'

extract is a MySQL function that isn't part of the ANSI SQL standard, and from the comments it seems you're trying to use it with MS-Access. Instead, you could consider using the datepart function which is more-or-less the MS-Access equivalent. Additionally, as lad2025 noted in his comment, you have a redundant comma after Orderyear:

SELECT DATEPART("yyyy", Starting_Date) AS orderyear
FROM   pgme
WHERE  id = 1

Syntax Error (missing operator) in the query expression

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;

MS ACCESS 2010 Syntax error (missing operator) in query expression in List Box

As you have discovered, Access SQL does not support queries of the form

SELECT x, DISTINCT y FROM z

If you haven't done so already, try

SELECT DISTINCT [Exhibit Recording].ReferenceNo, [Exhibit Recording].AOM FROM [Exhibit Recording];

(Notice that DISTINCT immediately follows SELECT. Access SQL supports DISTINCT across the entire query, but not on individual columns.)

If that doesn't give you distinct values for [AOM] and you really need them then you'll have to use a GROUP BY query that arbitrarily chooses a [ReferenceNo] to go along with each [AOM] value:

SELECT First([Exhibit Recording].ReferenceNo), [Exhibit Recording].AOM FROM [Exhibit Recording] GROUP BY [Exhibit Recording].AOM;

---

Related Topics