forms not populating data in date field in table

[asoe209]

Hello, just a quick one od some thing I have come across today.



I have a form, and on this form I have 3 date fields that i enter a date that something was tested, the other 2 date fildes I have put calculations in to calculate 12 months on the the test date and a cound down of how many days are left.

e.g.

Date one. 01/01/2013
date two 09/04/13 calculated to 98 days from date one wit the following =DateAdd("d",98,[LastPMI])
date 3 just counts down, so no real concern with this one, but date 2 is the one tat i need. as its the due date.

I have just notice that the date that calculates the future date does not put a date in my table that the form is linked to. so I now im getting wrong date data.

Is there some thing that can be done to corect this or will I just have to enter that dates manually.

[RuralGuy]

Maybe you will find this link helpful: http://allenbrowne.com/casu-14.html

[Bob Fitz]

You would not usually save the "due date" to a table because it is a calculation. The calculation should be done where ever and when ever it is needed using the expression: =DateAdd("d",98,[LastPMI])
See: http://allenbrowne.com/casu-14.html

[asoe209]

but the date that is generats is not saved to the table so it does not run the correct date in queries and reports

[Bob Fitz]

but the date that is generats is not saved to the table so it does not run the correct date in queries and reports

So use the calculation in those queries and reports.

[RuralGuy]

Did you review the link both Bob and I supplied?

[asoe209]

Hi, yes I have read the link and I dont quite get how this will fix the problem on m form. as I still need the due date to be calsulated/workout for me

i just do get why when it populates the date on my form at present but des not add tha to the table.

[RuralGuy]

As Bob said, you do not need the calculated dates in the table in order to calculate them in a query. How about trying to show us how you are attempting to use the calculated dates and we can then show you how to do the calculations right there.

[asoe209]

ok ill et some screen shots up after lunch.

[RuralGuy]

If you are using a table as the RecordSource of a Report or Form then changing that to a query and posting the SQL view of it will be all it takes. No pictures needed.

[asoe209]

hi, Ihave gone ahead and done screen prints any way.

as you can see, I put the date in the form, and the next dates are worked out. o table view you can see the date I entered but the following dates still blank even through shown on form.

the query uses the the due date as to what is due for a month, but if nothing is in the field this will not happen. I then run my report base on the query.

[asoe209]

to foollow on, please remember I am a oob at this so some tings said do go over the head....

[RuralGuy]

Is the "NewLiftingTestDue" query the RecordSource of the PlantManagementForm?

[kwolfel]

Under the Property Sheet > Data tab, do you have the Record Source filled in with the field from your table that you expect the calculated dates to fill? It sounds like the Record Source may contain the actual calculation. If this is true, I would recommend moving the calculation to an "After Update" event in Property Sheet > Event tab of the first date field (that you enter). From here, you can use visual basic code to assign the other two date fields to equal the calculations you want.

Example code:

Private Sub [Name of Date Combo/Textbox]_AfterUpdate()

Me![Name of Date2 Combo/Textbox] =DateAdd("d",98,[LastPMI])
Me![name of Date2 Combo/Textbox] = calculation

End Sub

This setup would allow you to enter a date into the first field and the "AfterUpdate" would automatically populate the other two field textboxes based on the calculations. By setting the Record Source of the other two date fields to the appropriate fields in the table, the calculated dates should get saved into the table.