Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    1

    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.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    Quote Originally Posted by bambam
    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.
    http://www.dbforums.com/t995623.html

    it got some time stuff in it
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    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

    FinalCalc: IIf([FirstCalc]<0,24+[FirstCalc],[FirstCalc])

    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.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •