Find Due Date From X months in Access 2007

55 pts.
Tags:
Access Database
Microsoft Access 2007
Microsoft Access database
I have a database that has several different training topics each that a due at different intervals.

I have a date completed field [Date completed] and reoccurs every x months [recurrence] and then the next due date [due date].

How do I get my database to calculate and store the next due date? Please use (For dummies) explanation.

Date_Completed  = Date Field Recurrence          = TXT Field Due_Date            = Date Field


 



Software/Hardware used:
Micorsoft Access 2007

Answer Wiki

Thanks. We'll let you know when a new response is added.

You could use the DateAdd Function.

<pre>= DateAdd("m",[recurrence],[Date completed])</pre>

If you want to update it with a SQL statement, it should be something like this:

<pre>UPDATE your_table
SET [due date] = DateAdd("m",[recurrence],[Date completed])
WHERE <conditions></pre>

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Desnyder2001
    I still don't understand where to put this code to update the field in the table
    55 pointsBadges:
    report
  • carlosdl
    If you want to update the field for all the existing records, you would need to create a query with the UPDATE statement mentioned above. If you want to update it automatically when new records are added, then you will need to modify the entry form to do the calculation when some event occurs. Let us know how and when you want to do it, and we might be able to provide more help.
    69,420 pointsBadges:
    report
  • Desnyder2001
    I am working on the database right now. Again, I am a very novice user. I am in the military and trying to track the training of some of my troops. I would like to input the information in my form and have it calculate the due date and store that information in the table. I will then create a query to give me a list of all people that are within 30 days of the due date so they can start working on the training. Thanks for your quick response. Please help.
    55 pointsBadges:
    report
  • carlosdl
    You could use the AfterUpdate trigger. In design mode (right click on the form and select 'design View'), click on the [Recurrence] field and in the Property Sheet select the 'event' tab, then click on the "After Update" event and click on the "..." button to the right, and select "Code Builder". Write this code inside the Recurrence_AfterUpdate procedure created:
     [Due Date] = DateAdd("m", [Recurrence], [Date Completed])
    Save your work. This will calculate the due date every time you modify the Recurrence field in the form. To make sure the calculation is performed only when valid data exists in both fields (date completed and recurrence) you can add some validations:
    If [Date Completed] <> "" And [Recurrence] <> "" Then
        [Due Date] = DateAdd("m", [Recurrence], [Date Completed])
    End If
    You might want to add this same code to the AfterUpdate event of the 'date completed' field, so the due date is recalculated when any of these two fields is modified.
    69,420 pointsBadges:
    report
  • Desnyder2001
    sounds like we are on the right track, when I put the code [Due Date] = DateAdd(”m”, [Recurrence], [Date Completed]) in the "After Update" code builder, it gives me a run time error. Any thoughts?. Also just so you know there is nothing else in the database that I am trying this on. I have a test database with only those three fields in the table. The (Date Completed) is a date/time field, the (Recurrence) is a Number field and the (Due Date) is a date/time field. Not sure if this matters.
    55 pointsBadges:
    report
  • Desnyder2001
    ****** I just put your code in a query and it works. But I want it to work in the Form... We are getting close. Thank you for your help, I am very excited about getting this done.
    55 pointsBadges:
    report
  • Desnyder2001
    OK I got it. ******* I renamed the tables without any spaces i.e. Due_Date instead of Due Date and it worked. I don't know why this is, but it worked. Thank you so much.
    55 pointsBadges:
    report
  • carlosdl
    Can you post the complete error message ? Also, when does the error occur ?
    69,420 pointsBadges:
    report
  • carlosdl
    Great ! Glad it worked. (Disregard my last comment, I didn't see you last post until I submitted mine).
    69,420 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following