Problem with calculating days between dates

[mixup]

I have a form with two date fields: "Initiation_date", and "Correction_date". There is a third field called "Duration". I would like to store the values of all three fields in the corresponding table. "Duration" (Long Integer) would be the number of days from "Initiation_Date" to "Correction_Date" if Correction date is there, or to Date() if Correction date is Null.

I can use an unbound text box to get "Duration_cal" using the following as a control source:

=IIf(IsNull([VCor_date]),DateDiff("d",[VInit_date],Date()),DateDiff("d",[VInit_date],[VCor_date]))

But I cannot get that value (No.of Days) in to the "Duration" field. The reason I want to have "Duration" stored as a number is that it is being used in a query for another calculation (total days used for selected records).

Is there a simple way to accomplish this?

Thanks

 

[gromit]

Hi -

As tempting as it often is, there are good reasons NOT to store values in tables. If the underlying data ever changes, then the "calculated" item would not be correct.

The best approach would be to make a "preliminary" query that contains all the information that you need. Then just base your other query (total days for selected records) on this preliminary query. The preliminary query serves as a dynamic copy of the table data and you don't need to worry about synchronization.

It looks like you already have the formula for the Duration_cal. Just use this as an expression for your preliminary question.

Hope that helps, post back if you have more questions.

- g