Date range query

[Dave888]

I'm trying to add a date range to a form I've created with a start date and end date. Here is the code I'm using, but what do I need to change on this so that I can still continue to do queries based on other frields if the two date range frileds are blank

((VPO.OrderDate) Between [forms]![calculatepayment1].[qbegdate3] And [forms]![calculatepayment1].[qenddate3]));

 

[Brianwarnock]

Where (IsNull([forms]![calculatepayment1].[qbegdate3] and [forms]![calculatepayment1].[qenddate3]) or VPO.OrderDate) Between [forms]![calculatepayment1].[qbegdate3] And [forms]![calculatepayment1].[qenddate3]);

I think

Brian

 

[Dave888]

pasted that in and now I get no results from the query no matter what I'm searching on. is it a problem because I have the "having" clause?

this is the entire code of the query

SELECT VPOItems.VPONumber, VPO.VendorCode, VPO.OrderDate, VPOItems.ProductID, VPOItems.TotalCost, [TotalCost]*[FirstDeposit%] AS [First Payment Amount], [TotalCost]*[SecondDeposit%] AS [Second Payment Amout], [TotalCost]*[FinalPayment%] AS [Final Payment Amount], Sum(VPOPayment.VPOAmountPaid) AS SumOfVPOAmountPaid, [SumOfVPOAmountPaid]/[TotalCost] AS [Percent Paid], VPOItems.QtyOrdered, VPOReceiving.QtyReceived
FROM (Vendors INNER JOIN VPO ON Vendors.VendorCode = VPO.VendorCode) INNER JOIN ((VPOItems LEFT JOIN VPOPayment ON (VPOItems.ProductID = VPOPayment.ProductID) AND (VPOItems.VPONumber = VPOPayment.VPONumber)) LEFT JOIN VPOReceiving ON (VPOItems.VPONumber = VPOReceiving.VPONumber) AND (VPOItems.ProductID = VPOReceiving.ProductID)) ON VPO.VPONumber = VPOItems.VPONumber
WHERE (((IsNull([forms]![calculatepayment1].[qbegdate3] And [forms]![calculatepayment1].[qenddate3]) Or [VPO].[OrderDate]) Between [forms]![calculatepayment1].[qbegdate3] And [forms]![calculatepayment1].[qenddate3]))
GROUP BY VPOItems.VPONumber, VPO.VendorCode, VPO.OrderDate, VPOItems.ProductID, VPOItems.TotalCost, [TotalCost]*[FirstDeposit%], [TotalCost]*[SecondDeposit%], [TotalCost]*[FinalPayment%], VPOItems.QtyOrdered, VPOReceiving.QtyReceived
HAVING (((VPOItems.VPONumber) Like [forms]![calculatepayment1].[qVPO3] & "*") AND ((VPO.VendorCode) Like [forms]![calculatepayment1].[qvendor3] & "*"));

 

[Brianwarnock]

I've never used a where and a having in one query, the where applies before grouping and the having after, I don't see why they shouldn't work , however the problem with blindly copy and pating is that you can miss typos ther is an itinerent ) here
Or [VPO].[OrderDate])

Brian

 

[Dave888]

works now, thanks!