SQL Statement - Selecting between dates using dateadd() and dateserial()

[John Crozier]

Hello everyone,

I was recently tasked to convert some of the functions employees use Excel for into Access. One of these functions is seeing which invoices are due to be paid this month. Surely this is simple I thought, and so it would be - if I had a field with a duedate in it. However I am using the sum of two fields as my date and thus, I feel, I have a problem. It's the end of the day and I'm knackered, can anyone spot my Query error?

The below code is one of the many different ways I've tried to skin this cat...
(There are plenty of records with both [TermsDays] AND [TermsDate] NULL.)
In Query; Datatype mismatch in this case. I assume with the DateAdd?

SELECT tblPurchaseOrderDetail.*, tblPurchaseOrderDetail.TermsDays, tblPurchaseOrderDetail.TermsDate
FROM tblPurchaseOrdersHeader INNER JOIN tblPurchaseOrderDetail ON tblPurchaseOrdersHeader.OrderNumber = tblPurchaseOrderDetail.OrderNumber
WHERE (((tblPurchaseOrderDetail.TermsDate) Is Not Null) AND ((DateAdd("d",[TermsDays],[TermsDate]))>=DateSerial(Year(Now()),Month(Now()),1) And (DateAdd("d",[TermsDays],[TermsDate]))<DateSerial(Year(Now()),Month(Now()),1)));

Any help would be greatly appreciated.

John

[nico5038]

I would probably use:

SELECT tblPurchaseOrderDetail.*, tblPurchaseOrderDetail.TermsDays, tblPurchaseOrderDetail.TermsDate
FROM tblPurchaseOrdersHeader INNER JOIN tblPurchaseOrderDetail ON tblPurchaseOrdersHeader.OrderNumber = tblPurchaseOrderDetail.OrderNumber
WHERE tblPurchaseOrderDetail.TermsDate Is Not Null 
AND tblPurchaseOrderDetail.TermsDays Is Not Null 
AND [TermsDate] + [TermsDays] between DateSerial(Year(Date()),Month(Date()),1) And DateSerial(Year(Date()),Month(Date())+1,0)));

Adding days can be done using simply a "+", being faster as the DateAdd function. Using the 0 in the Dateserial will give the last day of a month.
Using "Between" is faster as it's just one comparison instead of two when using >= and <=.

Guess you would also need a report showing records without a Date and/or term, as they will be skipped...

Nic;o)

[John Crozier]

Thats bang on, thanks very much Nico! Don't know how I got stuck on that, but everythings simple in retrospect right? I'm grateful for the quick concise answer!

John

[nico5038]

Getting things simple is the hardest part <LOL>

Glad I could help and success with your application !

Nic;o)