Microsoft Access – Fix JOIN ON Syntax Error in Access 2016

ms accessms-access-2016syntax

I am querying a couple tables, and for some reason Microsoft Access 2016 continually informed me that my SQL query JOIN ON syntax is incorrect. Specifically, the JOIN syntax is highlighted, followed by the ON syntax when removing that (the latter for clearer reasons based on prior knowledge of SQL JOIN ON syntax).

Here is the code:

SELECT Column1, Column2, Table1.Column3
FROM Table1 
JOIN Table2
    ON Table1.Column3 = Table2.Column3
   AND Column4 = '{NAME}'
ORDER BY Column4;

Is there something incorrect about this syntax? By the way, this is using ANSI-89 Syntax.

Best Answer

Access requires INNER JOIN. The simple JOIN (which should default to INNER JOIN according to the SQL standard) is not allowed in Access.

Related Solutions

Microsoft Access – JOIN on Derived Table Not Working

Perhaps rewrite the statement so Microsoft Access can more easily understand the requirements.

Something like:

SELECT
  T1.Product, 
  T1.Color, 
  T1.SEQMAX,
  Table1.Attribute
FROM (
    SELECT Table2.Product   
        , Table2.Color
        , SEQMAX
    FROM Table2
        LEFT JOIN
        (
            SELECT
              Table1.Product, 
              MAX (Table1.Seq) as SEQMAX
            FROM Table1
            GROUP BY Table1.Product
        ) AS MAXTABLE ON  Table2.Product = MAXTABLE.Product
    ) AS T1
    LEFT JOIN Table1
      ON T1.SEQMAX = Table1.Seq 
        AND T1.Product = Table1.Product;

Ms-access – Access 2010-2016 Compatibility Error

_{Community wiki answer:}

That particular error message is the key:

Compile error: cannot find project or library

Opening the office 2010 database in 2016 removed all of the VBA references to 14.0 Object Libraries in the References menu, and replaced them with 16.0, which is not present in Office 2010.

Broken references are to be expected when the .mdb is opened in an older version. It happens when there are references to Office libraries (Word, Excel, etc). Edit the references (in the VB Editor) in the oldest version (the one that doesn't run the code).

Best Answer

Related Solutions

Microsoft Access – JOIN on Derived Table Not Working

Ms-access – Access 2010-2016 Compatibility Error

Related Question