date fill-in

[Charlie Klein]

Hello Access people:

I am a computer simpleton. For an organizationl
membership database I set up in Access, I need to create a
formula that will fill in the "Membership Expires" field
by looking at the "Date Joined" field, adding one year,
and then inserting the date of the last day of the month
of that year. E.g.: John Smith joins 7/16/04. When that
date is entered in the "Date Joined" field, I want
the "Membership Expires" field to automatically fill in
7/31/05 (with the possibility that the expiration date can
be changed manually if necessary). Can someone tell me
how to do that?

Thanks,
Charlie Klein

 

[Graham Mandeno]

Hi Charlie

The following expression will calculate the required date:
ExpiryDate = DateSerial(Year(JoinDate) + 1, _
Month(JoinDate) + 1, 0)

What it does is calculate the date one day before the first day of the month
one year and one month in advance of the given date.

 

[Dan]

There's probably an easier way to do this...but you can
use the following formula:

ExpirationDate: CDate(DatePart("m",[Date Joined])+1
& "/1/" & DatePart("yyyy",[Date Joined])+1)-1

-Dan