Return all dates for 'next month' irrespective of year or day
in [Queries]
|
From: Andy79 on 2 Mar 2010 09:26 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
From: Andy79 on 2 Mar 2010 09:29 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));
From: Daryl S on 2 Mar 2010 11:28 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 "Andy79" wrote: > 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)); > > . >
From: John Spencer on 2 Mar 2010 13:38 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 wrote: > 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
From: Andy79 on 2 Mar 2010 13:57 Thanks Daryl, that function is indeed the exact answer to my problems!! Many Thanks Andy On Mar 2, 4:28 pm, Daryl S <Dar...(a)discussions.microsoft.com> wrote: > 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 > > "Andy79" wrote: > > 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)); > > > .
|
Pages: 1 Prev: Eliminating duplicates in Access 2007 Next: crosstab query child table count |