By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Help to calculate a time in the future

P: 2
I would like to create a field that calculates a date that is 39 months from a date I am entering on a form. Once I fill in a date in a field, I would like to have Access calculate the next field which would be 39 months later. What formula would I enter to achieve that effect? Thank you!
Feb 12 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,638
I would like to create a field that calculates a date that is 39 months from a date I am entering on a form. Once I fill in a date in a field, I would like to have Access calculate the next field which would be 39 months later. What formula would I enter to achieve that effect? Thank you!
Assuming the Text Box containing your Date is txtDate, and the New Date will be contained in txtNewDate, then in the AfterUpdate() Event of txtDate type the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDate_AfterUpdate()
  2.     If Not IsDate(Me![txtDate]) Then
  3.        Exit Sub
  4.     Else
  5.        Me![txtNewDate] = DateAdd("m", 39, Me![txtDate])
  6.     End If
  7. End Sub
Feb 12 '07 #2

100+
P: 176
You can also use this in your textbox Control Source:

=DateAdd("m",39,[txtDate])
Feb 12 '07 #3

P: 2
Thanks for your Help!
Assuming the Text Box containing your Date is txtDate, and the New Date will be contained in txtNewDate, then in the AfterUpdate() Event of txtDate type the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDate_AfterUpdate()
  2.     If Not IsDate(Me![txtDate]) Then
  3.        Exit Sub
  4.     Else
  5.        Me![txtNewDate] = DateAdd("m", 39, Me![txtDate])
  6.     End If
  7. End Sub
Feb 12 '07 #4

P: 12
I would like to create a field that calculates a date that is 39 months from a date I am entering on a form. Once I fill in a date in a field, I would like to have Access calculate the next field which would be 39 months later. What formula would I enter to achieve that effect? Thank you!

Use the dateadd(). In your query design view, click in the box where the field you want to calculate is and type
=DateAdd("m",+39, [the field name for the date entered])
once you click out of the box you will see
Expr1:=DateAdd("m",+39, [the field name for the date entered])
Just change the word "Expr1:" to whatever you want the column name to be and there you go. Be sure there is a colon between your column name and the equal sign that starts the expression ... good luck
Feb 12 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.