Today's date and add 365 for second date?

  • Thread starter Thread starter Retarmy
  • Start date Start date
R

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.
 
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.
 
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
 
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


BruceM said:
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.

Retarmy said:
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.
Click to expand...
Click to expand...
 
Back
Top