DateDiff

[Guest]

I have 3 fields on a form, (DateEntered), (DateComplaintClosed) and
(DaysToCompeteComplaint).

What I want to have happen is once a date is entered into
(DateComplaintClosed) field, I want the result of (DateEntered) and
(DateComplaintClosed) to populate the field (DaysToCompleteComplaint).

I can get it to display this calculation by putting
=DateDiff("d",[DateEntered],[DateComplaintClosed]) in the control source of
the DaysToCompleteComplaint but I need this value stored in the underlying
table in the DaysToCompleteComplaint field also. The results are to be used
in other calculations as to avg. complaint completion times, total complaint
times etc.

 

[Ken Snell [MVP]]

Are you sure you need to store that value? Because it can be calculated from
the other two fields' values at any time, you should not be storing it in a
table. Instead, use the DateDiff expression in any queries that you're using
to calculate the value as you need it. This also means that you won't need
to worry if someone changes one of the other fields' value(s) and your
program doesn't update that calculated field.