Query returning values only if 1st of month 1

[hjgoldstein]

Dear all,

I have a requirement for a query to give me a list of audits required "next" month.

My query is based on a table which includes, among others, the following fields:

CustLastAudit - format is Short Date - Date the customer was last audited.

CustAudFreq - format is Long Integer - Frequency of audits required in months.

I have an expression in the query "NextAudDue" which calculates the date of the next audit bu adding the frequency in months to the last audit date. I have set a criterion which should give me the results of next month's audits.

My SQL is as follows:

SELECT TblCust.CustName, TblCust.CustGeoArea, TblCust.CustPhone1, TblCust.CustContact, TblCust.CustACMA, TblCust.CustAudPriority, TblCust.CustLastAudit, TblCust.CustAudFreq, DateAdd("m",Nz([CustAudFreq],0),[CustLastAudit]) AS NextAudDue
FROM TblCust
WHERE (((DateAdd("m",Nz([CustAudFreq],0),[CustLastAudit]))=DateSerial(Year(Date()),Month(Date())+1,1)));

The query returns nothing. If I change the criterion to ...+2... it returns one record of several, and I have worked out that this is because the record it is returning has a date of 1st of the month after next. I have tested this with other numbers of months added, and think that this is the case.

Can I set up the format of the expression so that it works as a mmm-yyyy date and ignores the dd bit?

What do I need to include in (or exclude from) the SQL to enable all of "next" month's audits to be returned by the query?

Hope you can help. I need to demonstrate this tomorrow :-(

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

 

[PHV]

WHERE Format(DateAdd("m",Nz([CustAudFreq],0),[CustLastAudit]),"yyyymm")=Format(DateSerial(Year(Date()),Month(Date())+1,1),"yyyymm");

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

 

[hjgoldstein]

That's worth a star in any language. Thanks.

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