Computing an End Date

  • Thread starter Thread starter Dwight
  • Start date Start date
D

Dwight

I need to determine an end date by adding a set number of years to the start
date and then taking the date to the end of the month.

Here is what I use to have but the requirement changed to the last day of
the month.

!TEND = DateAdd("d", -1, DateAdd("yyyy", !Tenure, !NewTourDt))

Thanks in advance for any help you can provide me.

Dwight
 
There may be a more elegant way but this should work:

!TEND = DateSerial(Year(DateAdd("d", -1, DateAdd("yyyy", !Tenure,
!NewTourDt)),Month(DateAdd("d", -1, DateAdd("yyyy", !Tenure, !NewTourDt)+1),0)
--
Bob Larson
Access MVP
Access World Forums Administrator
Utter Access VIP

Tutorials at http://www.btabdevelopment.com

__________________________________
 
Dwight

Take a look at the DateSerial() function. A trick you can use is to add one
to the month, then use the "0"th day. This will always get you the last day
of the previous month. First do your DateAdd(), then use DateSerial().

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I need to determine an end date by adding a set number of years to the start
date and then taking the date to the end of the month.

Here is what I use to have but the requirement changed to the last day of
the month.

!TEND = DateAdd("d", -1, DateAdd("yyyy", !Tenure, !NewTourDt))

Thanks in advance for any help you can provide me.

Dwight
Click to expand...
Using the DateSerial function, the last day of any month is the 0 day
of the following month.
Let's add 5 years to the [ADate] field.

=DateSerial(Year(DateAdd("yyyy",5,[ADate])),Month(DateAdd("yyyy",5,[ADate]))+1,0)

Change [ADate] to whatever the appropriate field name is.
 
Back
Top