Access Missing Operator on Query

Question

I'm stuck on trying to figure out why I'm getting a missing operator error not his query.

The Query is as follows:

SELECT DISTINCT
  FLEET.regno
, SUBMODEL.submodel
, FLEET.icao
, FLEET.startyr
, CARRIERS.sector
FROM (FLEET
INNER JOIN SUBMODEL
ON FLEET.[M/S/Variant] = SUBMODEL.[M/S/Variant]
INNER JOIN LOOKUP
ON
  (SUBMODEL.SUBMODEL = LOOKUP.SUBMODEL
  AND FLEET.ICAO = LOOKUP.ICAO)
INNER JOIN CARRIERS
ON FLEET.icao = CARRIERS.ICAO)
WHERE (
  LOOKUP.[ASM/ac] is not null
  OR LOOKUP.[ATM/ac] is not null
) AND FLEET.status = 'ACTIVE';

Access 2010 is throwing the following error:

Syntax error (missing operator in query expression 'FLEET.[M/S/Variant] = SUBMODEL.[M/S/Variant] INNER JOIN LOOKUP ON (SUBMODEL.SUBMODEL = LOOKUP.SUBMODE'.

I've tried putting parentheses in different places but still running into issues. Is there something I'm missing here.

. . Access needs parentheses around each join combination. It's ugly, but needed. — Gordon Linoff
Can you be a little more specific - after the word INNER JOIN ( )? - or at least point out where i'm missing them? I understand that it needs parentheses but haven't seen a good example anywhere exactly showing what i'm supposed to write. Thanks — Jeef

Jeef Jeef · Accepted Answer · 2014-03-04T15:51:04

We fixed it:

SELECT DISTINCT
  FLEET.regno
, SUBMODEL.submodel
, FLEET.icao
, FLEET.startyr
, CARRIERS.sector
FROM ((FLEET
INNER JOIN SUBMODEL
ON FLEET.[M/S/Variant] = SUBMODEL.[M/S/Variant])
INNER JOIN LOOKUP
ON
  SUBMODEL.SUBMODEL = LOOKUP.SUBMODEL
  AND FLEET.ICAO = LOOKUP.ICAO)
INNER JOIN CARRIERS
ON FLEET.icao = CARRIERS.ICAO
WHERE (
  LOOKUP.[ASM/ac] is not null
  OR LOOKUP.[ATM/ac] is not null
) AND FLEET.status = 'ACTIVE';

Access Missing Operator on Query

1 Answers