Add a year to a date field auto update to another date field (1 Viewer)

[AccessNewbie2010]

Hello all,

New to this forum and a complete access beginner so please be gentle! I am creating a membership contacts database and would like to know how to automatically add a year to a membership start date and output it into another field (membership end date) within the same table. Is this possible without using SQL programming? I have tried to do this through the form properties of the field by trying to use the DateAdd function on the default value, through the table properites in design view again on the default value and also with creating another table and using a couple of append queries, but have not got it to work. Many thanks for your help.

Eric

 

[John Big Booty]

In your table for your StartDate, whilst the table is in Design view put Date() in the Default Value field this will put today's date as the default date.

Now for your EndDate put DateAdd("YYYY",1,Date()) this will put todays date plus one year.

Now if the default StartDate is changed you will need some code in that fields after update event to handle that change, something like the following should do the trick;

Me.EndDate = DateAdd("YYYY",1,[StartDate])

 

[John Big Booty]

Having thought about this, a little, the end date is really a calculated field and technically should not be stored, as it can be calculated at any time using;

DateAdd("YYYY",1,[StartDate])

and then compared to the current date using something like the following;

If Date() > DateAdd("YYYY",1,[StartDate]) Then
     MsgBox "Membership current till " & [StartDate]
Else
     MsgBox "Membership Expired"
End If