Today's date and add 365 for second date?

[Retarmy]

Have a date field that is a manual input, what I need is a way to add 365
days to show the expiration date. An automatic insertion into the Expired
date field would be exelent.

Johnnie, a Novice user trying to fix an existing database.

 

[Linq Adams via AccessMonster.com]

Where StartDate is the textbox you're entering the original date in:

Private Sub StartDate_AfterUpdate()
If IsDate(Me.StartDate) Then
Me.ExpirationDate = DateAdd("d", 365, Me.StartDate)
Else
MsgBox "You MUST enter a Valid Date!"
Me.ExpirationDate.SetFocus
Me.StartDate.SetFocus
End If
End Sub

 

[Linq Adams via AccessMonster.com]

Of course, the previous code will give you a different day going into or out
of a leap year. You may want to use

Private Sub StartDate_AfterUpdate()
If IsDate(Me.StartDate) Then
Me.ExpirationDate = DateAdd("yyyy", 1, Me.StartDate)
Else
MsgBox "You MUST enter a Valid Date!"
Me.ExpirationDate.SetFocus
Me.StartDate.SetFocus
End If
End Sub

which will always give you the same month/day a year later.

 

[Klatuu]

What about leapyear?
Wouldn't dateadd("yyyy",1,Me.StartDate) be better?

 

[BruceM]

There is no need to store the expiration date. You could just use DateAdd
in a calculated query field, and use the query as the record source for a
form or report. At the top of a blank column in query design view:
ExpDate: DateAdd("yyyy",1,[YourDateField])
Use your actual field name in place of YourDateField.
If you literally want 365 days you could use "d" in DateAdd:
ExpDate: DateAdd("d",365,[YourDateField])
Of course, leap years will give you a slightly different result than just
adding a year.
You could use the same expression as the Control Source of an unbound text
box:
=DateAdd("yyyy",1,[YourDateField])
However, the query will make the date available in any form or report based
on the query.
See Help for more information about DateAdd.

 

[Dale Fye]

Easiest way is probably to put the following (replace txt_Expiration_Date
with whatever you have named the text box containing your expiration date) in
the AfterUpdate event of the textbox where you enter the purchase date
(assuming this is some sort of purchase).

Private Sub txt_Purchase_Date_AfterUpdate()

Dim dtPurDate as date

If not isdate(me.txt_Purchase_Date) then
msgbox "Purchase date is not a valid date"
me.txt_Purchase_Date.setfocus
exit sub
else
dtPurDate = me.txt_Purchase_Date
me.txt_Expiration_Date.Value = DateSerial(Year(dtPurDate) + 1, _

Month(dtPurDate), _

Day(dtPurDate))
'or
'me.txt_Expiration_Date.Value = Dateadd("d", 365,
me.txt_Purchase_Date)
Endif

End Sub

'The first method actually add as year to the date, the second adds 365 days.

HTH
Dale

 

[Evi]

I agree about not storing the Expiration date but it may be worth storing
the number of days that are added onto the Startdate to make an Expiration
date (in case that needs to be changed later).
Evi

There is no need to store the expiration date. You could just use DateAdd
in a calculated query field, and use the query as the record source for a
form or report. At the top of a blank column in query design view:
ExpDate: DateAdd("yyyy",1,[YourDateField])
Use your actual field name in place of YourDateField.
If you literally want 365 days you could use "d" in DateAdd:
ExpDate: DateAdd("d",365,[YourDateField])
Of course, leap years will give you a slightly different result than just
adding a year.
You could use the same expression as the Control Source of an unbound text
box:
=DateAdd("yyyy",1,[YourDateField])
However, the query will make the date available in any form or report based
on the query.
See Help for more information about DateAdd.

Have a date field that is a manual input, what I need is a way to add 365
days to show the expiration date. An automatic insertion into the Expired
date field would be exelent.

Johnnie, a Novice user trying to fix an existing database.