Prev: Eliminating duplicates in Access 2007
Next: crosstab query child table count
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