Need Help For query (1 Viewer)

rajarshi

New member
Local time
Today, 11:40
Joined
Dec 6, 2016
Messages
1
Can anyone tell me why am I getting error in the below query?
Need some advice as I am new to Access


SELECT PTblPOs.PONo, PTblOrderHeaders.OHSBNo, PTblOrderLines.OLName, PTblOrderHeaders.OHInvApprover, PTblOrderHeaders.OHInvCCRec, PTblOrderHeaders.OHEntCodeRef, PTblOrderHeaders.OHVenCodeRef, PTblInvoices.InvNumber, PTblInvoices.InvDate, PTblInvoices.InvQuant, PTblInvoices.InvAmt, PTblInvoices.InvText, PTblInvoices.InvComment, PTblInvoices.InvIPNoRef, PTblInvoices.InvGRReqDate, PTblPOs.POSelfDelivery, PTblPOs.POLine, PTblGRs.GRQuant, PTblGRs.GRAmt, PTblGRs.GRNo_iShop, PTblGRs.GRNo_SAP
FROM (PTblOrderHeaders
INNER JOIN
PTblOrderLines ON PTblOrderHeaders.OHSBNo = PTblOrderLines.OLSBNoRef)
INNER JOIN
(PTblPOs
INNER JOIN
((PTblInvoices LEFT JOIN PTblInv2GR ON PTblInvoices.[InvID] = PTblInv2GR.[I2G_InvIDRef])
(PTblGRs ON PTblGRs.GRID = PTblInv2GR.I2G_GRIDRef )) ON PTblPOs.POLineID &PTblPOs.POLineID = PTblInvoices.[InvPOLineIDRef]&PTblGRs.[GRPOLineIDRef)
ON PTblOrderLines.OLID = PTblPOs.PO_OLIDRef
WHERE (((PTblPOs.POSelfDelivery)=False) AND ((PTblInv2GR.I2G_InvIDRef) Is Null);
 
Last edited:

JHB

Have been here a while
Local time
Today, 08:10
Joined
Jun 17, 2012
Messages
7,732
What error (No/Description) do you get then?
 

Grumm

Registered User.
Local time
Today, 08:10
Joined
Oct 9, 2015
Messages
395
Not sure a big deal but you miss a closing ')' in the WHERE :
Code:
WHERE [COLOR="Red"]([/COLOR]((PTblPOs.POSelfDelivery)=False) AND ((PTblInv2GR.I2G_InvIDRef) Is Null);
 

plog

Banishment Pending
Local time
Today, 01:10
Joined
May 11, 2011
Messages
11,638
My guesses are 'Join not supported' for the error and this line of code as the culprit:

ON PTblPOs.POLineID &PTblPOs.POLineID = PTblInvoices.[InvPOLineIDRef]&PTblGRs.[GRPOLineIDRef)

You don't use the ampersand (&) to JOIN on multiple fields. You use the keyword AND, and you have to explicity do each comparison. Also, you have a missing right bracket at the end of it. So maybe the error you are seeing is 'Unrecognized field', but fix that and you'll get the Join unsupported one.
 

Users who are viewing this thread

Top Bottom