Trigger an automatic calculation when data is entered.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table with the following three fields:

Begin_Date End_Date Duration

I would like to automatically calculate duration when data for Begin_date
and End_date are entered. Is there a way to do this?
 
Yes, but you would not do it in the table. Taht is redundant. You simply
calculate it in your report, query, or form when you need it.

If you stored it in your tabel, what would happen if one of the numbers
changed?

Hope that helps.

Rick B
 
Rick

Thanks for help. The dates will not change once entered. I don't wish to use
a saved query or report to calculate the field. I need it in the table itself.

Thanks,
 
Again, you don't do this in the table. It is not correct database design.
It is redundant. It increases the size and overhead of your database.

When you build a query, form or report calculate it.

If you will have users that need to pull this and don't know how to do so,
then create a saved query that they can use as their record source. In the
query add a new column with something like the following...

Duration: End_Date - Begin_Date


Access does not have a facility to do what you ask, because it should not be
done.

Obviously, you could add the field to a table and run update queries to plug
in the number in your existing records. You could add calculations in your
data entry form and plug those into the field, etc. But it is not good
design.

You stated the dates won't change. Are you sure? No one will ever make a
typo?


I'd recommend doing this according to good practices and established
normalized design principles. All these MVPs with years of experience
couldn't be that wrong could they?

Good luck,

Rick B
 
If you can explain why you NEED it stored in the table, perhaps someone will
be willing to help you do that. However, Rick B is right that you don't need
it there. Access tables do not have triggers and, thus, you will need to
calculate the difference in a form anyway, so might as well do it either in
a query, report, or a form.
 
Back
Top