Finding dates 11 months older than the current month

[gronsky]

Howdy.

I have a form(Members) based on a table(Members)
what I need to do is on the first of each month run a query that would locate all the membership dates(MemDate) in the table that are 11 months old to the current month.

Could this be done using a command button on the form(Members)itself? And how would it be done?

Appreciate any help on this.

Thanks in advance

 

[THoey]

Sure, this can be done. Look at the DateAdd function. It should be added to your WHERE clause. I think it will be something like:

WHERE DatePart("m", MembershipDate) = Datepart("m", DateAdd ("m", -11, Date())) and
DatePart("yyyy", MembershipDate) = Datepart("yyyy", DateAdd ("yyyy", -11, Date()))

I didn't test this, so it might take some playing... Terry M. Hoey

 

[jebry]

Hi!

Try this:

DateDiff("m", DateSerial(Year(MemDate), Month(MemDate), 1), Date())

This will give you the number of months between the present date and the their member date, adjusting the membership date to the first of the month in which they became a member.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[gronsky]

Thanks for the replies.

I got the thing to do what I want, almost.

In the table, there is a field; Date_Dues_Paid.
There is also a field; Dues_Expires.

On my form, I enter the date the dues were paid. In the control source for another text box, Expires, i have =DateAdd("m","12",[Date_Dues_Paid]). This displays the info I want in the form but how do I get the resulting value from the above exp to be stored in the Dues_Expires field in my table?

Thanks

 

[RichUK]

You should not store Dues_Expire it's a calculated field, view the query if you want to see expiry dates.