Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2005
    Posts
    79

    Unhappy Unanswered: Query - Calculate Difference Between Two Times

    I have searched the forums, but have not found exactly what I am looking for on this on an Access query. I have 2 fields in the database that are formatted as text. One is start time and the other is end time. The formatting for both fields is hh:nn:ss. I am trying to create a query in which the time between the two fields is displayed in hours, minutes and seconds in one field after the query is run. For instance if the start time is 16:00:00 and the end time is 17:36:00, the field that I have labeled "duration" in the query should be 1:36:00 (1 hour and 36 minutes and 0 seconds). In the query, I have tried

    duration: datediff("h", [starttime],[endtime])

    However, this only gives me hour, n only give me minutes, and s only gives me seconds. How can I get all three together in the format 00:00:00 (hh:nn:ss) in one field after the query is run in my database. Any help would be greatly appreciated.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Place this function in a standard module. If you create a new module to do this, name the module anything except ElapsedTime.

    Code:
    Public Function ElapsedTime(Start As Date, Finish As Date) As String
    'Calculates elapsed time between 2 date/times and
    'parses it out into Hours-Minutes-Seconds in HH:MM:SS format
    Dim HoursLapsed, SecondsLeft, MinutesLapsed, SecondsLapsed As Long
    
    TotalSeconds = DateDiff("s", Start, Finish)
    
    HoursLapsed = Int(TotalSeconds / 3600)
    
    SecondsLeft = TotalSeconds Mod 3600
    
    MinutesLapsed = Int(SecondsLeft / 60)
    
    SecondsLapsed = SecondsLeft Mod 60
    
    ElapsedTime = Format(HoursLapsed, "00") & ":" & Format(MinutesLapsed, "00") & ":" & Format(SecondsLapsed, "00")
    
    
    End Function
    Now, in the Query Grid for your query, create a new, calculated field like this:

    Duration: ElapsedTime([StartTime],[EndTime])
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2005
    Posts
    79
    I created a new module and named it Total Time. See below.

    Public Function TotalTime(Start As Date, Finish As Date) As String
    'Calculates elapsed time between 2 date/times and
    'parses it out into Hours-Minutes-Seconds in HH:MMS format
    Dim HoursLapsed, SecondsLeft, MinutesLapsed, SecondsLapsed As Long

    TotalSeconds = DateDiff("s", Start, Finish)

    HoursLapsed = Int(TotalSeconds / 3600)

    SecondsLeft = TotalSeconds Mod 3600

    MinutesLapsed = Int(SecondsLeft / 60)

    SecondsLapsed = SecondsLeft Mod 60

    TotalTime = Format(HoursLapsed, "00") & ":" & Format(MinutesLapsed, "00") & ":" & Format(SecondsLapsed, "00")


    End Function



    I then went to my query and then entered

    Duration: TotalTime([StartTime],[EndTime])

    I get the error "undefined function "total time" in expression."

    Am I missing something

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    You don't use the name of the module

    Duration: TotalTime([StartTime],[EndTime])

    you use the name of the function

    Duration: ElapsedTime([StartTime],[EndTime])
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    And to repeat/clarify, the module and the function can not have the same name, which may be the cause of your error.
    Paul

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Thanks, Paul! I hadn't noticed that he'd changed the name of the function, too!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Nov 2005
    Posts
    79

    Sorry

    He is actually a she. I just realized what you were saying in reference to the module and function not having the same name. I ensured that they do not have the same name. I renamed the function back to elapsed time and changed the query back to elapsed time as well. Sorry, I misunderstood you. I ran the query, and at first got nothing even though stepping through the code showed that everything was calculating correctly. I then noticed that I had spelled elapsedtime wrong. It is working. Thank you guys so much. Your assistance is greatly appreciated. I do have another question. I believe that this this code accounts for time when 60 seconds is actually 1 minute. Is my assumption correct.
    Last edited by miracleblake; 04-29-09 at 13:06.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Did you remember to change this line to the correct function name?

    TotalTime = Format(HoursLapsed, "00") & ":" & Format(MinutesLapsed, "00") & ":" & Format(SecondsLapsed, "00")

    If so, can you post the db, or a sample of it?
    Paul

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by miracleblake
    He is actually a she... I do have another question. I believe that this this code accounts for time when 60 seconds is actually 1 minute. Is my assumption correct.
    Sorry about the gender thing! I used to always use "he/she" when it wasn't clear, but after thousands of posts, here and elsewhere, it got to be kind of tiresome.

    I'm not clear as to your question "when 60 seconds is actually 1 minute."

    It calculates the difference in Seconds and then parses this into hours/minutes/seconds, and 60 seconds is usually 1 minute. Is a minute different in your world?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Nov 2005
    Posts
    79

    Working Now

    I had posted that it wasn't working, and re-edited after I noticed the incorrect spelling in my code. I changed everything to ElapsedTime and it is functioning. Because I had misspelled the word in the code it didn't put anything anywhere. So, it appears to be working and working well. I do have another question for you. Now that I have the duration of time calculated for each row. On a report, how can I calculate the total number of hh:nn:ss of all durations (columns) in the footer. This would include seconds rolling into minutes, minutes rolling into hours (60 minutes then adds another hour into calculation.) I hope I make sense. Thanks.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so that would suggest you are not using option explicit in your code/forms etc....
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a hidden control which has the difference in say hours, then send the sum of that hidden control to the function....
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2005
    Posts
    79
    Quote Originally Posted by Missinglinq
    Sorry about the gender thing! I used to always use "he/she" when it wasn't clear, but after thousands of posts, here and elsewhere, it got to be kind of tiresome.

    I'm not clear as to your question "when 60 seconds is actually 1 minute."

    It calculates the difference in Seconds and then parses this into hours/minutes/seconds, and 60 seconds is usually 1 minute. Is a minute different in your world?
    lol. 60 seconds is a minute in my world too, but only if I am having a good day. From what you are saying, 61 minutes would parse out to 1 hour and 1 minutes or 1:01:00.

  14. #14
    Join Date
    Nov 2005
    Posts
    79

    It wasn't there

    Quote Originally Posted by healdem
    so that would suggest you are not using option explicit in your code/forms etc....
    It wasn't there, so I just put it in. It has been so long, but now I remember, it makes sure what you type has been defined. Thank you.

  15. #15
    Join Date
    Nov 2005
    Posts
    79

    Report Summing - Duration

    The times that are in my database are

    1:00:00
    0:30:00
    0:46:00

    In a text box on my report I entered as a control source

    =Sum(Format([duration],"hhnnss"))

    After running my report, I get

    17600

    It is just adding the numbers and not rolling over minutes to hours and the formatting is not correct. I guess it should be 1:16:00. Any help would be appreciated. Thanks.

Posting Permissions

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