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
– Gordon Linoff, Commented Mar 4, 2014 at 15:44
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, Commented Mar 4, 2014 at 15:48

Yawar · Accepted Answer · 2014-03-10 04:17:14Z

2

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';

edited Mar 10, 2014 at 4:17

Yawar

11.7k4 gold badges53 silver badges88 bronze badges

answered Mar 4, 2014 at 15:51

Jeef

27.3k23 gold badges87 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yawar Over a year ago

That's odd--in my experience, Access doesn't support multiple conditions in the on clause.

Collectives™ on Stack Overflow

Access Missing Operator on Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related