Return all dates for 'next month' irrespective of year or day

[Andy79]

Hello,

Sorry to ask such a simple question;

I would like my query to return all rows were the my 'date' field is
any day in the next month. The year & day of month are not relevant.

I have been getting stuck using (DatePart("m",Date())+1), this works
fine except on December at which point the expression returns 13, and
not 1, and therefore does not find the entries with a date in Jan.

I also need to a similar sets of data where my date field is
(DatePart("m",Date())-1) or ...-3 but again where the months cross
over Dec/Jan this breaks down..

Many Thanks for your help.

"Query to select all MemberID with MembershipStarted date in next
calendar month":

SELECT tblMembersList.MemberID
FROM tblMembersList
WHERE (((DatePart("m",tblMembersList.MembershipStarted))=DatePart("m",
(Date()+1))));

Andy

 

[Andy79]

Here is the correct version of the above query

SELECT MembersList.MemberID, DatePart("m",[MembersList].
[MembershipStarted])
FROM MembersList
WHERE (((DatePart("m",[MembersList].
[MembershipStarted]))=DatePart("m",Date())+1));

 

[Daryl S]

Andy79 -

The trick is to add (or subtract) a month from today's date, and use that
month:

SELECT MembersList.MemberID, DatePart("m",[MembersList].
[MembershipStarted])
FROM MembersList
WHERE (((DatePart("m",[MembersList].
[MembershipStarted]))=DatePart("m",DateAdd("m",1,Date())));

 

[John Spencer]

Try the following

SELECT tblMembersList.MemberID
FROM tblMembersList
Month(MembershipStarted) = (Month(Date()) Mod 12) + 1

The expression will return the values 0 (December) to 11 (November). Adding
one to that will give you 1 (January) to 12(December).

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Andy79]

Thanks Daryl, that function is indeed the exact answer to my
problems!!

Many Thanks
Andy

Andy79 -

The trick is to add (or subtract) a month from today's date, and use that
month:

SELECT MembersList.MemberID, DatePart("m",[MembersList].
[MembershipStarted])
FROM MembersList
WHERE (((DatePart("m",[MembersList].
[MembershipStarted]))=DatePart("m",DateAdd("m",1,Date())));

--
Daryl S

Here is the correct version of the above query

SELECT MembersList.MemberID, DatePart("m",[MembersList].
[MembershipStarted])
FROM MembersList
WHERE (((DatePart("m",[MembersList].
[MembershipStarted]))=DatePart("m",Date())+1));

.