Report needs to get updated values by doing calculation

[Lhuffst]

I have a report that retrieves records based on a query that works fine. Query has 3 fields: Open Date, Close Date and #daysOpen with #daysopen being calculated. When the reports are run, I need to make sure that the records are up-to-date. I can do this in a function but that updates the entire database and takes to long. I need to find a way that the recalculation takes place only the the resultant records in the query. Any ideas would be greatly appreciated -- at a loss where to begin. Thanks lhuffst

 

[dhookom]

Is #daysOpen a field? If not, why would you need to perform any updates? I would expect #daysOpen to be a column calculated in the query based on the Open Date and Close Date.

You shouldn't normally be storing values that can be calculated on-the-fly.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Lhuffst]

Yes #daysopen is a field. I agree about the calculation not being stored but the original programmer was storing the calculated value in the field and the calculation was triggered when they modified/added the records. Normally this works fine but if they go straight to the reports form (bypassing the actual record) then the data isn't updated. Hence the reason I'm trying to figure a way around the problem.
Thanks again.
lhuffst

 

[dhookom]

You should be able to set the value of [#daysopen] in the after update events of [Open Date] and [Close Date]. You don't have to wait for the before update of the record/form.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Lhuffst]

good idea. Thanks I'll give it a try