is my query too complex?

[Dave888]

SELECT VPO.VendorCode, VPOPayment.PaymentID, PaymentID.AccountingReference, PaymentID.PaymentDate, VPO.VPONumber, VPO.OrderDate, VPOItems.ProductID, VPOItems.TotalCost, Sum(nz(vpopayment.VPOAmountPaid)) AS [Amount Paid]
FROM VPO INNER JOIN (PaymentID RIGHT JOIN (VPOItems LEFT JOIN VPOPayment ON (VPOItems.ProductID=VPOPayment.ProductID) AND (VPOItems.VPONumber=VPOPayment.VPONumber)) ON PaymentID.PaymentID=VPOPayment.PaymentID) ON VPO.VPONumber=VPOItems.VPONumber
GROUP BY VPO.VendorCode, VPOPayment.PaymentID, PaymentID.AccountingReference, PaymentID.PaymentDate, VPO.VPONumber, VPO.OrderDate, VPOItems.ProductID, VPOItems.TotalCost
HAVING
((VPO.VendorCode) Like forms!PaymentSearch1.qvendor1 & "*") AND
((VPOPayment.PaymentID) Like forms!PaymentSearch1.qPaymentID1 & "*") AND
((VPOItems.ProductID) Like forms!PaymentSearch1.qproductID1 & "*") AND
((IsNull(forms!paymentsearch1.qbegdate1 And forms!paymentsearch1.qenddate1) Or PaymentID.PaymentDate Between forms!paymentsearch1.qbegdate1 And forms!paymentsearch1.qenddate1)) AND
((IsNull(forms!paymentsearch1.qbegvpo1 And forms!paymentsearch1.qendvpo1) Or VPO.VPONumber Between forms!paymentsearch1.qbegvpo1 And forms!paymentsearch1.qendvpo1));


Access keeps saying the expression is either typed incorrectly or too complex. It worked fine up until I added the last IsNull statemenet and I've checked it to make sure the statement is chosing the right data.

 

[namliam]

Lets start by re-formatting the sql to make it more readable:

SELECT VPO.VendorCode
	, VPOPayment.PaymentID
	, PaymentID.AccountingReference
	, PaymentID.PaymentDate
	, VPO.VPONumber
	, VPO.OrderDate
	, VPOItems.ProductID
	, VPOItems.TotalCost
	, Sum(nz(vpopayment.VPOAmountPaid)) AS [Amount Paid]
FROM VPO 
INNER JOIN (PaymentID 
            RIGHT JOIN (VPOItems 
                        LEFT JOIN VPOPayment ON (VPOItems.ProductID=VPOPayment.ProductID) 
                                            AND (VPOItems.VPONumber=VPOPayment.VPONumber)
                                  ) ON PaymentID.PaymentID=VPOPayment.PaymentID
            ) ON VPO.VPONumber=VPOItems.VPONumber
GROUP BY VPO.VendorCode
		, VPOPayment.PaymentID
		, PaymentID.AccountingReference
		, PaymentID.PaymentDate
		, VPO.VPONumber
		, VPO.OrderDate
		, VPOItems.ProductID
		, VPOItems.TotalCost
HAVING
    ((VPO.VendorCode) Like forms!PaymentSearch1.qvendor1 & "*") 
AND ((VPOPayment.PaymentID) Like forms!PaymentSearch1.qPaymentID1 & "*") 
AND ((VPOItems.ProductID) Like forms!PaymentSearch1.qproductID1 & "*") 
AND (      (   IsNull(forms!paymentsearch1.qbegdate1 And forms!paymentsearch1.qenddate1) 
            Or PaymentID.PaymentDate Between forms!paymentsearch1.qbegdate1 And forms!paymentsearch1.qenddate1
		   )
    ) 
AND (      (   IsNull(forms!paymentsearch1.qbegvpo1 And forms!paymentsearch1.qendvpo1) 
            Or VPO.VPONumber Between forms!paymentsearch1.qbegvpo1 And forms!paymentsearch1.qendvpo1
           )
    );

Can you spot your mistake now?

 

[Dave888]

no.... LOL

sorry, still a rookie at most of this stuff.
can I have a hint?

 

[MSAccessRookie]

Copy the reformatted SQL into a NOTEPAD or MS Word Document, and you should see the issues.

HINT: the Characters "(" and ")" should occur in matched pairs.

 

[namliam]

HINT: the Characters "(" and ")" should occur in matched pairs.

Actually no...

Even rookies must have learned SQL?

How do Left and Right joins work???

 

[Dave888]

I changed the join for VPO to VPOitems and it works now.

 

[namliam]

Well.. yes... You know what you did wrong now??

You cannot mix left and right joins in this way... Its a mess!

We western european people think top to bottom, left to right....
Forget about right joins and always only use left ones to prevent this problem.