2 INNER JOINs possible? 2

[Slippenos]

Microsoft JET Database Engine error '80040e14' 

Syntax error (missing operator) in query expression 'A.DataRequestNo = B.DataRequestNo INNER JOIN tblIsoData C ON A.DataRequestNo = C.DataRequestNo'.

DataRequestNo is the PK for all 3 tables. It originates from tblDataRequests

               tblNinaInfo
              /
             /
            /
           /
tblDataRequests
           \
            \
             \
              \
               tblIsoData

SELECT A.Month, A.DataRequestNo, A.CsStatus, A.AccountManager, A.CustServ, B.NinaStatus, C.designaccept
FROM tblDataRequests A INNER JOIN tblNinaInfo B 
ON A.DataRequestNo = B.DataRequestNo 
INNER JOIN tblIsoData C
ON A.DataRequestNo = C.DataRequestNo
WHERE

[red]Tools | Internet Options | Clear History[/red]

 

[Dagon]

Remove the redundant where clause which has no conditions associated with it.

 

[Slippenos]

Full statement:

SELECT A.Month, A.DataRequestNo, A.CsStatus, A.AccountManager, A.CustServ, B.NinaStatus, C.designaccept 
FROM tblDataRequests A INNER JOIN tblNinaInfo B 
ON (A.DataRequestNo = B.DataRequestNo) 
INNER JOIN tblIsoData C
ON (A.DataRequestNo = C.DataRequestNo) 
WHERE
A.SellPlant = '" & plant & "'
AND A.AccountManager = '" & acctman & "'
AND A.CustServ = '" & customer & "'
AND A.CsStatus = '" & statusdrf & "'
AND B.NinaStatus = '" & statusnina & "'
AND C.IsoData = '" & statusiso & "'
ORDER BY A.Month;

[red]Tools | Internet Options | Clear History[/red]

 

[Slippenos]

I meant:
[red]AND C.designaccept = '" & statusiso & "'[/red]

[red]Tools | Internet Options | Clear History[/red]

 

[rac2]

Seeing the quote marks in your query leads me to think that you are building the query in a program somewhere, then submitting it to the database for processing. Hence, the error may be in the string-building code and not in the SQL.

If that is true, then quote marks are needed at the beginning and ending of the string.

"SELECT ... 
...
ORDER BY A.Month"

Or if you are providing parameters to the query for a person to supply the values when they run the query, then the quote marks should be omitted entirely with Access; as well as the concatenation operators.

SELECT ...
...
WHERE A.SellPlant = plant
AND A.AccountManager = acctman
..

 

[Slippenos]

I have the quote marks before SELECT and at the end of my SQL, I neglected to place them in.
I also deleted the quote marks around the supplied values.

I still get:

Microsoft JET Database Engine error '80040e14' 

Syntax error (missing operator) in query expression 
'(A.DataRequestNo = B.DataRequestNo) 
 INNER JOIN tblIsoData C ON 
 (A.DataRequestNo = C.DataRequestNo)'.

I don't know .. any suggestions?


[red]Tools | Internet Options | Clear History[/red]

 

[PHV]

... FROM [highlight]([/highlight]tblDataRequests A
INNER JOIN tblNinaInfo B ON A.DataRequestNo=B.DataRequestNo[highlight])[/highlight]
INNER JOIN tblIsoData C ON A.DataRequestNo=C.DataRequestNo
WHERE ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Slippenos]

Thanks PHV, that was it.

[red]Tools | Internet Options | Clear History[/red]