Access syntax error missing operator

Question

So I'm working in access trying to make a table. I've got all the code set up, but when I try to view the database, it gives me an error.

Syntax error (missing operatior) in query expression 's.SupplierID = p.SupplierID LEFT JOIN OrderDetails AS od     
on p.ProductID = od.ProductID LEFT JOIN Orders AS o     
on od.OrderID = o.OrderID LEFT JOIN Customers AS c     
on o.CustomerID = c.CustomerI'

However here is the full code:

Select  p.ProductName AS 'Out of Stock Product', o.OrderID, c.CompanyName, c.ContactName, c.Address + ' ' + c.City +', ' + IsNull(c.StateOrRegion+ ', ', '')+ c.Country+', ' + c.PostalCode AS 'ADDRESS'
From Suppliers AS s LEFT JOIN Products AS p
on s.SupplierID = p.SupplierID LEFT JOIN OrderDetails AS od 
on p.ProductID = od.ProductID LEFT JOIN Orders AS o 
on od.OrderID = o.OrderID LEFT JOIN Customers AS c 
on o.CustomerID = c.CustomerID
    Where Discontinued = 1
    Order By p.ProductName

(as for some reason it cuts off the 'D' in the last CustomerID thing)

Any ideas on where I'm going wrong?

EDIT: So I found out that I'm supposed to add parenthesis to the joins, but now it's giving me another problem.
Updated code:

Select  p.ProductName AS 'Out of Stock Product', o.OrderID, c.CompanyName, c.ContactName, c.Address + ' ' + c.City +', ' + IsNull(c.StateOrRegion+ ', ', '')+ c.Country+', ' + c.PostalCode AS 'ADDRESS'
From (Suppliers AS s LEFT JOIN Products AS p
on s.SupplierID = p.SupplierID) LEFT JOIN 
(OrderDetails AS od ON p.ProductID = od.ProductID) LEFT JOIN (Orders AS o 
on od.OrderID = o.OrderID) LEFT JOIN (Customers AS c 
on o.CustomerID = c.CustomerID)
    Where Discontinued = 1
    Order By p.ProductName

Error is: "Syntax error in JOIN operation", with the highlighted 'on' in all caps for your viewing convenience.

What table is Discontinued in?

Gordon Linoff
– Gordon Linoff

2016-12-13 00:16:11 +00:00
Commented Dec 13, 2016 at 0:16 — Gordon Linoff
– Gordon Linoff, Commented Dec 13, 2016 at 0:16
Discontinued was in Products

Firestar001
– Firestar001

2016-12-16 00:16:26 +00:00
Commented Dec 16, 2016 at 0:16 — Firestar001
– Firestar001, Commented Dec 16, 2016 at 0:16

Firestar001 · Accepted Answer · 2016-12-12 23:39:56Z

1

So here's the final code:

Select  p.ProductName AS 'Out of Stock Product', o.OrderID, c.CompanyName, c.ContactName, c.Address, c.City , c.StateOrRegion, c.Country, c.PostalCode
From 
(((
(Suppliers AS s LEFT JOIN Products AS p
on s.SupplierID = p.SupplierID) LEFT JOIN 
OrderDetails AS od
on p.ProductID = od.ProductID) LEFT JOIN Orders AS o 
on od.OrderID = o.OrderID) LEFT JOIN Customers AS c 
on o.CustomerID = c.CustomerID)
    Where Discontinued = 1
    Order By p.ProductName

I had to add parenthesis 'pulling in' the other join items. And in access I can't link multiple columns as one thing. Derp de dur

answered Dec 12, 2016 at 23:39

Firestar001

571 silver badge8 bronze badges

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

1 Comment

HackSlash Over a year ago

It's okay to mark your own answer as the correct one. You get points and it closes the Q out.

Collectives™ on Stack Overflow

Access syntax error missing operator

(as for some reason it cuts off the 'D' in the last CustomerID thing)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

(as for some reason it cuts off the 'D' in the last CustomerID thing)

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related