asked on

Extracting Next months data based on a date field

Hello experts,
I have a table holding a date field (Maintenance visits due).
I need to be able to run a report at any time in the current month that shows visits due next month.
So in December i need to see records for Jan etc etc.
My date field is "NextMaint" and I can a query that gives me the month for that 'Month: Format([nextmaint],"mm")' and the current month 'Currentdate: Format(Now(),"mm")'
However, I am a bit lost from there. Any help greatly appreciated.

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

You can select all records for next month with a query like below. This selects all dates greater or equal to the first day of the next month, and less or equal to the last day.

SELECT *
FROM tblTest
WHERE (((tblTest.NextMaint)>=DateSerial(Year(DateAdd("m",1,Now())),Month(DateAdd("m",1,Now())),1)
AND (tblTest.NextMaint)<=DateSerial(Year(DateAdd("m",2,Now())),Month(DateAdd("m",2,Now())),1)-1));

Open in new window

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Bill Prew

And probably a little safer for the boundary days in case they have time components...

SELECT *
FROM tblTest
WHERE tblTest.tstDate>=DateSerial(Year(DateAdd("m",1,Now)),Month(DateAdd("m",1,Now)),1) 
AND tblTest.tstDate<DateSerial(Year(DateAdd("m",2,Now)),Month(DateAdd("m",2,Now)),1);

Open in new window

»bp

Pawan Kumar

You can use like below -
DATEVALUE.

SELECT * FROM yourtableName
WHERE NextMaint BETWEEN DATEVALUE(Now()) AND DATEVALUE(DATEADD( "m",1, Now()))

Open in new window

iainmacleod

ASKER

Thank you both very much.
I went with Bills solution in the end, but great to have the knowledge of both.
What a great resource this site is...