DateSerial syntax looking for next month... 1

[hjgoldstein]

I'm getting awfully frustrated with this and would appreciate any help.

I have a table which includes, among other things "CustNextAudDue" which is a date field.

I need to query this in various ways:

1. Query on all records where "CustNextAudDue" is next month
2. Query on all records where "CustNextAudDue" is this month
3. Query on all records where "CustNextAudDue" is any time in the next six months.

Here's the SQL for option 1

SELECT TblCust.CustGeoArea, TblCust.CustName, TblCust.CustPhone1, TblCust.CustContact, TblCust.CustLastAudit, TblCust.CustAudFreq, TblCust.CustNextAudDue, TblAuditors.AudName, QryAudList.FinalPriority
FROM TblAuditors RIGHT JOIN (QryAudList RIGHT JOIN TblCust ON QryAudList.CustName = TblCust.CustName) ON TblAuditors.AudID = TblCust.CustAuditor
WHERE (((TblCust.CustNextAudDue)=DateSerial(Year(Date()),Month(Date())+1,1)) AND ((QryAudList.FinalPriority)<4))
ORDER BY TblCust.CustGeoArea, TblCust.CustName;

which produces no results at all.

Any advice or pointers (or code) would be more than welcome.



It is time for pacifists to stand up and fight for their beliefs.

 

[PHV]

SELECT ...
FROM ...
WHERE TblCust.CustNextAudDue BETWEEN DateSerial(Year(Date()),Month(Date())+1,1) And DateSerial(Year(Date()),Month(Date())+2,0)
AND QryAudList.FinalPriority<4
ORDER BY ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hjgoldstein]

PHV,

You are, indeed, the man! (person!)

It is time for pacifists to stand up and fight for their beliefs.