Calculated fields for dates

[jjake]

I have a basic maintenance schedule that i am trying to put together and i have a problem.

I have 3 Tables as seen in the relationships below [tblPMTask],[tblPMFrequency],[tblPMTaskCompletion]

I am trying to get the due date to populate with a date calculated by the [StartDate] + the amount of days in the Count field of tblPMFrequency.

I will then have a continuous form that lists all PM Tasks that have due dates equal to or past the "real time" date (Which i also do not know how to do ). When said due date is then complete via the completed tasks the count will take the latest date and add the count to it and display in the due date field. I'm sure i'm going about this the wrong way but i'm sure you catch my drift.

 

[pbaldy]

I personally wouldn't store the due date, I'd calculate it from the last completion date (or start date if not done yet). I haven't used the calculated field, so not sure it can refer to a different table. My gut says not. You can use the DateAdd() function when filling out start date if you want.

 

[jjake]

Ok so I took your advice on not storing the due date. How would I go about having the count table add the correct amount of days/months to the start date/last complete date in a form.

I made a form that shows the completion date using a query as well as start date and a due date field.

 

[pbaldy]

You should be able to use the DateAdd() function. Given your design, looks like you'd use month for the interval.

 

[The_Doc_Man]

jjake - here is a different person's viewpoint (but leading to a similar conclusion).

When you have computed results like this, two questions come up.

#1 - is the computed result dynamic? I.e. can it be changed after the record is initially stored, based on actions in other tables? If the answer is YES then you need to remember (or learn) that forms, reports, and queries only require a recordset as input. You can get recordsets from tables, ... but you can also get them from queries. Yes, a query can feed a query. So if there is an inherently dynamic nature regarding the computed result, perhaps it should not exist in the "obvious" table where you think it belongs. It might belong in a query based on that table, possibly JOINed to the table from which the dynamic information is derived.

#2 - is the computed value static once defined? If so, you should take pains to use a query or other method to fill in the blanks on the new record. If you were doing this in a bound form, it would be trivial to have some of the computed fields initialized in the form's _Current event.

#3 - is the problem that you don't define the records in an order that allows #2 to occur? Then my suggestion for the #1 case above is not wrong.

There are purists who might say that once a table value is defined, it should be (relatively) static and you should use queries on child tables to see some classes of updates for dependent fields. I'm nowhere near a purist, but the situation you described seems to me to be best served by sticking with queries for the dynamic definitions that depend on other tables. Don't store the value statically in a table's field at all.

 

[jjake]

Thank you both for your input. It is very helpful.

So I have created a query for my form that displays the [task] and [startdate]. I also included a [count] field from my frequency column.

I made another text box on my form titled [Due Date]. I then used the following control.

[COLOR=#363636]=DateAdd("m",[Count],[StartDate])[/COLOR]

In my example the start date is 2/26/16. The [count] is "3" for 3 months.

For some reason instead of displaying 5/26/16 it shows 1/26/17. Any ideas?

 

[pbaldy]

One possibility is that because count is a reserved word, you're getting the record count instead of the field.

 

[jjake]

One possibility is that because count is a reserved word, you're getting the record count instead of the field.

This was my problem. Thanks.

 

[pbaldy]

No problemo.