Thread: Compute Time In Ms Access
05-12-04, 15:04 #1Registered User
- Join Date
- May 2004
Unanswered: Compute Time In Ms Access
I am trying to setup a database that will calculate cycle time. (I.E. 1st shift started on job "xyz" @ 2 pm and ended at 5pm = 3 hours of time spend on the job.) I am new to Access and have my table and form the way I want it. Does anyone have a formula that will work and where do I input this formula. My fields for time are Start time, End time. Any help is much appreciated. Thanks.
05-12-04, 15:59 #2(Making Your Life Easy)
Provided Answers: 10Originally Posted by bambam
- Join Date
- Feb 2004
- New Zealand
it got some time stuff in ithope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON
05-12-04, 17:11 #3Registered User
- Join Date
- Apr 2004
- Sydney Australia
If you only need to cover a 24 hour period this is a simple way that I use in a telemarketing data base.
Assume your "sign on time" is [Time 1] and "sign off time is [Time 2]
Make a query on your table and create two calculated fields as such
FirstCalc: ([Time 2]-[Time 1])*24
If you have not made calculate fields all that you do is type the field name in one of the blank columns after you have dragged down your table fields and then add a colon that is a :
In the above example the FirstCalc gives a negative answer if the sign on time is a pm and sign off is an am time
You now base your form on the query you have made and then add the field [FinalCalc] to your form.
If you do not want to make a query for your form's data source then you add three unbound text boxes on your form and for their data you enter the above preceded by = such as
=([Time 2]-[Time 1])*24
assuming the above textbox has been named FirstCalc then the seond textbox has entered for its data source
=IIf([FirstCalc]<0,24+[FirstCalc],[FirstCalc]) That will give the hours for each individual record displayed on your form
If the above textbox has been called FirstCalc then in the third unbound textbox you enter in its data source
=Sum([FinalCalc]) That will give the total number of hours for all of your records displayed in your form
After you have finished then give the first text box, that is the one with
=([Time 2]-[Time 1])*24 as its data source, zero dimensions for top, width, left and height and you will no longer be able to see it.