Renewal query

[quaddawg]

I've searched the fora and found very similar questions, but I haven't been
able to put it all together.
I have a membership database.
There is a "Date Paid" field, which is the date a person joins. The
membership is good for a year. We send three renewals: one 2 months before
expiration, one 1 month away, and a final one in the month of expiration.
I would like to hit a command button that would run three renewal letter
reports running off of three renewal queries.
The way I would like this to work is that the data enterer would hit the
renewal button on one day of the month (say the 1st, but potentially a later
date) and the queries would select all records for the three months in
question. So, if there are Date Paid records of 5/1/2005, 5/14/2005,
5/31/2005, etc., they would be selected by the final renewal query--but the
query would not select a 5/1/2004 record.

So this doesn't get me there: WHERE (((Month([DatePaid]))=Month(Date())+1));
because it pulls 2004 records.

And I'm having problems with DateAdd because I haven't figured out how to get
all records within the given month, regardless of date or time within the
month.

To summarize:
Is it possible to run a query at any given day within a month to select all
records from the same month a year ago, from a month period 11 months ago,
and from a month period 10 months ago?
Sorry so wordy, thanks for the help!

 

[KARL DEWEY]

Use this criteria on your "Date Paid" field --
Between DateAdd("m",-12,Date()) And DateAdd("m",-10,Date())

 

[Ken Sheridan]

For the same month last year:

WHERE FORMAT(DatePaid),"yyyymm") =
FORMAT(DATESERIAL(YEAR(DATE())-1,MONTH(DATE()),1),"yyyymm")

For 11 months ago:

WHERE FORMAT(DatePaid),"yyyymm") =
FORMAT(DATESERIAL(YEAR(DATE())-1,MONTH(DATE())+1,1),"yyyymm")

For 10 months ago:

WHERE FORMAT(DatePaid),"yyyymm") =
FORMAT(DATESERIAL(YEAR(DATE())-1,MONTH(DATE())+2,1),"yyyymm")

Even if the month 12 months ago is December this will still work as the
DateSerial function interprets month 13 as month January of the next year and
month 14 as February.

Ken Sheridan
Stafford, England

 

[John Spencer]

One more option which takes advantage of any indexes you have on the field.

Today is 5 /11 / 2006 the following gets all records where DatePaid is
between 5/1/2005 and 5/31/2005. It will fail to pickup records where
DatePaid has a time on 5/31/2005 that is after midnight. So you will need
to modify it if you are recording a time with the date.

WHERE DatePaid Between DateSerial(Year(Date()),Month(Date())-12,1) and
DateSerial(Year(Date()), Month(Date())-11,0)

11 months is the same statement replacing -12 with -11 and -11 with-10

Why does it work? DateSerial happily adds (or subtracts) any number of
months or days correctly and adjusts for years and months as needed. On
final trick is the the Zero day of any month is one less than the first day
of the month, so that is always the last day of the prior month. Think of
it as getting the first day of a month and subtracting one day (1-1=0).

 

[quaddawg]

Thanks for the response. After I removed the extra end parans after DatePaid
it worked great!

For the same month last year:

WHERE FORMAT(DatePaid),"yyyymm") =
FORMAT(DATESERIAL(YEAR(DATE())-1,MONTH(DATE()),1),"yyyymm")

For 11 months ago:

WHERE FORMAT(DatePaid),"yyyymm") =
FORMAT(DATESERIAL(YEAR(DATE())-1,MONTH(DATE())+1,1),"yyyymm")

For 10 months ago:

WHERE FORMAT(DatePaid),"yyyymm") =
FORMAT(DATESERIAL(YEAR(DATE())-1,MONTH(DATE())+2,1),"yyyymm")

Even if the month 12 months ago is December this will still work as the
DateSerial function interprets month 13 as month January of the next year and
month 14 as February.

Ken Sheridan
Stafford, England

I've searched the fora and found very similar questions, but I haven't been
able to put it all together.

[quoted text clipped - 23 lines]

and from a month period 10 months ago?
Sorry so wordy, thanks for the help!