fun date change script

[GoodLife22]

Hello everyone. I hope you all had a great 4th of July.

I have 2 date fields on my form. When a user enters a date in the first date field I want it to automatically add the date to the second date field. So far I have this:

Private Sub Date_Started_AfterUpdate()
Me.Date_Finished = Me.Date_Started
End Sub

This part works fine. Now here is where it is too tricky for me.

I need to change the date just a bit. I want to
KEEP MONTH
SWITCH TO FIRST DAY OF THE MONTH (always the first)
THEN ADD 3 YEARS TO THE DATE

So:
05/24/2011 will become 05/01/2014
07/13/2010 will become 07/01/2013
12/22/2012 will become 12/01/2015

Any ideas? Thanks to anyone spends some time looking into this.

 

[boblarson]

Well, if that algorithm is always going to be that, then you shouldn't be storing that in the table. It should just be calculated at run time with a query.

But if you insist:

Private Sub Date_Started_AfterUpdate()
Dim dteTemp As Date
 
dteTemp = DateAdd("yyyy", 3, Me.Date_Started)

   Me.Date_Finished = DateSerial(Year(dteTemp), Month(dteTemp), 1)
End Sub

 

[GoodLife22]

First off THANK YOU for the work. I will add it to my system right now and try it out. We have to keep track of it at the table level. We use this date to pull reports and export data from this system into another system.

 

[GoodLife22]

OK your script worked PERFECT. You are a great help there Mr Bob Larson

 

[boblarson]

First off THANK YOU for the work. I will add it to my system right now and try it out. We have to keep track of it at the table level. We use this date to pull reports and export data from this system into another system.

Umm, contrary to popular belief, you can use a query in 99% of the places you would use a table. So, it is highly unlikely that you would need to use the table but instead could use a query in place of the table.