Save a calculated result in a table using a query (Access 2007)?

Question

I have a form of multiple project sites where one of the fields is labelled "Project Start Date". I have another field in the form labelled "Projected Project Finish Date". Both fields are stored in a table labelled "General Project Info" (well, at least that's the source of the information for the form come from).

I could manually fill in the Proposed Project Finish Date by adding 10 days to the Project Start Date, but I would like to make it a calculated field, i.e once someone puts in the Project Start Date Access automatically calculates the Proposed Project Finished Date.

I am aware you can use a query to calculate this: New Query-->Include Project ID and Project Start Date fields, then make the third field--> Proposed Finish Date: DateAdd("d",10,[Project Start Date]). This produces a query result which has a column with all the sites, a column with the Project Start Date and a column with a date that is 10 days later. Perfect. But, how do I store those new results in my existed General Project Info table and have them appear in the form? I'm obviously a beginner and am missing something.

Thanks for any help in this matter.

Oh, I am using Access 2007.

Fionnuala Fionnuala · Accepted Answer · 2013-01-08T17:39:05

You can use the After Update event for Project Start Date on your form to update the Project End Date control to Project Start Date + 10, but if the answer is always + 10, why store the date at all? Just use your query to show the end date.

Private Sub StartDate_AfterUpdate()
     Me.EndDate = Me.StartDate + 10
End Sub

Or

SELECT StartDate, StartDate + 10 As EndDate FROM ATable

Edit

To add an After Update event, use the property sheet in form design view. Select [Event Procedure] and then click the three little dots. It will open up the code window and you can add code into the event. You will need to use the proper names for you controls, but if you type Me., intellisense will help you along with names of properties, methods and controls.

events

Save a calculated result in a table using a query (Access 2007)?

1 Answers