Results 1 to 9 of 9
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670

    Calculating Elapsed Time

    I am trying to calculate the elapsed time from when call was made to when call was returned. I have the date/time call was made in one box, and the date/time call was returned in another. I need to calculate the elapsed time between when the call was made and when it was returned. I tried to build a query, and this is the SQL of the query:L

    Code:
    SELECT tbl2.DateTimeCalled, tbl2.DateTimeReturned, Format([DateTimeReturned]-[DateTimeCalled],"hh:mm:ss") AS TAT  FROM tbl2  
    GROUP BY tbl2.DateTimeCalled, tbl2.DateTimeReturned;
    this works for the query, but ONLY if all of the information is same day, when there is greater than a 24 hour difference between the two the query is not displaying the TAT properly.



    Then the second part I was wanting to compute was to display the results of this query in a text box on my form, so that you can view the TAT on my form, instead of having to run the query and match up the data.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Would that be the default value for my text box I want to display the data in? Or would that be the SQL for the query?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    The link showed you what to do when calculating past midnight which is your original post.

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    How would I do this part.....

    Then the second part I was wanting to compute was to display the results of this query in a text box on my form, so that you can view the TAT on my form, instead of having to run the query and match up the data.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Quote Originally Posted by jo15765 View Post
    How would I do this part.....
    Have you tried:
    Format([DateTimeReturned] -1 -[DateTimeCalled],"hh:mm:ss")

  7. #7
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Using this format:

    Format([DateTimeReturned] -1 -[DateTimeCalled],"hh:mm:ss")

    Is not giving me an accurate difference.

    Does it make a difference if the date and time are in the same box?

  8. #8
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    Try these steps:
    1) Add these modules to your database:

    Code:
    Public Function HoursAndMinutes(interval As Variant) As String
    '*************************************************************
    ' Function HoursAndMinutes(interval As Variant) As String
    ' Returns time interval formatted as a hours:minutes string
    '*************************************************************
      Dim totalminutes As Long, totalseconds As Long
      Dim hours As Long, minutes As Long, seconds As Long
    
      If IsNull(interval) = True Then Exit Function
    
      hours = Int(CSng(interval * 24))
     
      ' 1440 = 24 hrs * 60 mins
      totalminutes = Int(CSng(interval * 1440))
      minutes = totalminutes Mod 60
     
      ' 86400 = 1440 * 60 secs
      totalseconds = Int(CSng(interval * 86400))
      seconds = totalseconds Mod 60
    
      ' Round up the minutes and adjust hours
      If seconds > 30 Then minutes = minutes + 1
      If minutes > 59 Then hours = hours + 1: minutes = 0
      HoursAndMinutes = hours & ":" & Format(minutes, "00")
    
    End Function
    
    Public Function ElapsedTimeString(dateTimeStart As Date, _
                                      dateTimeEnd As Date) _
                                      As String
    '*************************************************************
    ' Function ElapsedTimeString(dateTimeStart As Date,
    '                            dateTimeEnd As Date) As String
    ' Returns the time elapsed between a starting Date/Time and
    ' an ending Date/Time formatted as a string that looks like
    ' this:
    ' "10 days, 20 hours, 30 minutes, 40 seconds".
    '*************************************************************
      Dim interval As Double, str As String, days As Variant
      Dim hours As String, minutes As String, seconds As String
    
      If IsNull(dateTimeStart) = True Or _
        IsNull(dateTimeEnd) = True Then Exit Function
    
      interval = dateTimeEnd - dateTimeStart
      days = Fix(CSng(interval))
      hours = Format(interval, "h")
      minutes = Format(interval, "n")
      seconds = Format(interval, "s")
    
    ' Days part of the string
      str = IIf(days = 0, "", _
        IIf(days = 1, days & " Day", days & " Days"))
      str = str & IIf(days = 0, "", _
        IIf(hours & minutes & seconds <> "000", ", ", " "))
    
    ' Hours part of the string
      str = str & IIf(hours = "0", "", _
        IIf(hours = "1", hours & " Hour", hours & " Hours"))
      str = str & IIf(hours = "0", "", _
        IIf(minutes & seconds <> "00", ", ", " "))
    
    ' Minutes part of the string
      str = str & IIf(minutes = "0", "", _
        IIf(minutes = "1", minutes & " Minute", _
                      minutes & " Minutes"))
                     
      str = str & IIf(minutes = "0", "", _
                      IIf(seconds <> "0", ", ", " "))
    
    ' Seconds part of the string
      str = str & IIf(seconds = "0", "", _
        IIf(seconds = "1", seconds & " Second", _
            seconds & " Seconds"))
     
      ElapsedTimeString = IIf(str = "", "0", str)
    
    End Function
    2) Use this as your SQL for your query
    Code:
    SELECT tbl2.DateTimereturned, tbl2.DateTimecalled, ElapsedTimeString([DateTimeReturned],[DateTimecalled]) AS TAT
    FROM tblIMDatabase2
    WHERE tbl2.Received = True
    GROUP BY tbl2.DateTimecalled, tbl2.DateTimeReturned;
    3) Finally to view the results on your form create a text box on your form with this as the control source:
    =ElapsedTimeString([DateTimereturned],[DateTimecalled])


    **Let me know if you have any questions....I actually got all of this from a access book that I Have that I can reference for any follow up questions that could arise.

  9. #9
    jo15765's Avatar
    jo15765 is offline Expert
    Windows 7 Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    670
    Those show the days/hours/minutes just like I was needing, my only question is, if the time is greater than 24 hours, it shows a -1, is there a way to make it not be negative since it is actually only 1 day....

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 12
    Last Post: 02-22-2011, 03:39 PM
  2. Calculating Trip Time
    By CSUjr in forum Access
    Replies: 2
    Last Post: 08-06-2010, 05:06 AM
  3. Calculating turnaround time on workdays
    By mathonix in forum Queries
    Replies: 1
    Last Post: 04-22-2010, 05:34 PM
  4. Time Elapsed Problem
    By leejqs in forum Reports
    Replies: 6
    Last Post: 07-16-2009, 07:58 AM
  5. Need help with calculating Time total
    By Monoceros in forum Reports
    Replies: 1
    Last Post: 04-20-2009, 12:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums