Syntax Error

[Edgar Thoemmes]

Hi

Can anyone spot an error in this SQL? I am getting an error 'Syntax error or
missing operator in query expression'

SELECT tblSpendbySupplier.SuppCode, tblSuppliers.SupplierName,
Sum(tblSpendbySupplier.Amount) AS SumOfAmount
FROM (tblSpendbySupplier INNER JOIN tblSuppliers ON
tblSpendbySupplier.SuppCode = tblSuppliers.SupplierCode) INNER JOIN
(tblGLCodes INNER JOIN tblSpendbyGL ON tblGLCodes.GLCode =
tblSpendbyGL.GLCode) ON tblSpendbySupplier.URN = tblSpendbyGL.URN
WHERE (((tblSpendbySupplier.Date) Between #01/08/2004# AND #31/08/2004#))
AND (((tblGLCodes.Industry) =IN(0,1,2) AND
((tblSpendbySupplier.TransactionType) = 4))
GROUP BY tblSpendbySupplier.SuppCode, tblSuppliers.SupplierName,
tblSpendbySupplier.URN, tblGLCodes.Industry,
tblSpendbySupplier.TransactionType
ORDER BY Sum(tblSpendbySupplier.Amount) DESC;

Thanks

Edgar

 

[Michel Walsh]

Hi,



Seems there is a ) missing in the WHERE clause. May have some other error
too. I would personally use

(( ... INNER JOIN ... ON ... ) INNER JOIN .. ON ...) INNER JOIN ... ON ...


rather than


(... INNER JOIN ... ON ... ) INNER JOIN ( ... INNER JOIN ... ON ... ) ON
....



In theory, inner join can be performed in any order among themselves, as
long as it makes sense in the ON fields that are involved, but the first
formulation is less error prone, in syntax, since it is easier to spot which
fields can be used in the ON clause: only those which belong to the tables
mentioned up to that point.

( a INNER JOIN b ON a.f1=c.f1) INNER JOIN c ON b.f2=c.f2



is illegal since c.f1 is mentioned before implying the table c.




Hoping it may help,
Vanderghast, Access MVP