sql - Syntax error (missing operator) in query expression

Question

I have the following SQL Statement that works fine in SQL Server but in Access gets me an error

'ERM_HAZARD.[HAZARDID] = ERM_XREF_HAZARD_CAT.[HAZARDID] 
INNER JOIN ERM_HAZARDSTATUS 
ON ERM_HAZARD.HAZARDSTATUSID = ERM_HAZARDSTATUS.HAZARDSTATUSI'

---

 SELECT erm_hazard.hazardid    AS PKTrans,
       erm_hazard.extref      AS No,
       erm_cat.name           AS CaseType,
       erm_hazard.created     AS CaseDate,
       erm_hazard.name        AS Title,
       erm_hazard.description AS DescriptionTrans,
       erm_organisation.name  AS ResponsibleUnit,
       erm_hazard.reviewdate  AS CaseDeadline,
       erm_hazardstatus.name  AS Status,
       erm_organisation.name  AS UnitInCharge,
       erm_personel.name      AS PersonInCharge
FROM   (erm_personel
        INNER JOIN (erm_organisation
                    INNER JOIN erm_hazard
                            ON erm_organisation.[organisationid] =
                               erm_hazard.[organisationid])
                ON erm_personel.[personelid] = erm_hazard.[personelid])
       INNER JOIN (erm_cat
                   INNER JOIN erm_xref_hazard_cat
                           ON erm_cat.[catid] = erm_xref_hazard_cat.[catid])
               ON erm_hazard.[hazardid] = erm_xref_hazard_cat.[hazardid]
       INNER JOIN erm_hazardstatus
               ON erm_hazard.hazardstatusid = erm_hazardstatus.hazardstatusid
WHERE  (( ( erm_xref_hazard_cat.periodid ) = (SELECT Max(
                                             erm_xref_hazard_cat.periodid)
                                              FROM   erm_xref_hazard_cat) ))
ORDER  BY erm_xref_hazard_cat.periodid DESC,
          erm_hazard.hazardid,
          erm_cat.name  

Is this a Access SQL Size problem? Any ideas?

Answer 1

MS Access is very particular with the parentheses in queries. When you have multi-table joins a pair of parentheses must be around each join:

SELECT ERM_HAZARD.HAZARDID AS PKTrans, 
    ERM_HAZARD.EXTREF AS No, 
    ERM_CAT.NAME AS CaseType, 
    ERM_HAZARD.CREATED as CaseDate, 
    ERM_HAZARD.NAME AS Title, 
    ERM_HAZARD.DESCRIPTION AS DescriptionTrans,  
    ERM_ORGANISATION.NAME AS ResponsibleUnit, 
    ERM_HAZARD.REVIEWDATE AS CaseDeadline, 
    ERM_HAZARDSTATUS.NAME as Status, 
    ERM_ORGANISATION.NAME AS UnitInCharge, 
    ERM_PERSONEL.NAME AS PersonInCharge 
FROM ((((ERM_PERSONEL 
INNER JOIN ERM_HAZARD 
    ON ERM_PERSONEL.[PERSONELID] = ERM_HAZARD.[PERSONELID])
INNER JOIN ERM_ORGANISATION 
    ON ERM_ORGANISATION.[ORGANISATIONID] = ERM_HAZARD.[ORGANISATIONID])
INNER JOIN ERM_XREF_HAZARD_CAT 
    ON ERM_HAZARD.[HAZARDID] = ERM_XREF_HAZARD_CAT.[HAZARDID])
INNER JOIN ERM_CAT 
    ON ERM_CAT.[CATID] = ERM_XREF_HAZARD_CAT.[CATID])
INNER JOIN ERM_HAZARDSTATUS 
    ON ERM_HAZARD.HAZARDSTATUSID = ERM_HAZARDSTATUS.HAZARDSTATUSID
WHERE ERM_XREF_HAZARD_CAT.PERIODID= (SELECT MAX(ERM_XREF_HAZARD_CAT.PERIODID) 
                                     FROM ERM_XREF_HAZARD_CAT) 
ORDER BY ERM_XREF_HAZARD_CAT.PERIODID DESC,  ERM_HAZARD.HAZARDID, ERM_CAT.NAME