Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / January 2005

Tip: Looking for answers? Try searching our database.

Trigger an automatic calculation when data is entered.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jared - 25 Jan 2005 19:21 GMT
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?
Rick B - 25 Jan 2005 19:29 GMT
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

> 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?
Jared - 25 Jan 2005 19:39 GMT
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,

> 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.
[quoted text clipped - 12 lines]
> > I would like to automatically calculate duration when data for Begin_date
> > and End_date are entered. Is there a way to do this?
Rick B - 25 Jan 2005 19:56 GMT
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

> Rick
>
[quoted text clipped - 19 lines]
> > > I would like to automatically calculate duration when data for Begin_date
> > > and End_date are entered. Is there a way to do this?
Lynn Trapp - 25 Jan 2005 20:46 GMT
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.
Signature

Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

> Rick
>
[quoted text clipped - 23 lines]
>> > Begin_date
>> > and End_date are entered. Is there a way to do this?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.