Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: Calculating time duration

    Hello;

    Need resources for help on calculating duration of time. Using Access 2003 on an XP platform. I have a report that is fed from a query. The query obtains its data from a table where [StartTime] and [EndTime] are stored as Medium Time format. On the QBE grid I have a column with TotalTime: DateDiff("d",[EndTime],[StartTime]). This returns all zeros. When I use TotalTime: [EndTime]-[StartTime] I get a calculated result of 4:27 (using StartTime of 7:30AM and an EndTime of 11:57AM). I also use another column with Duration: Format([StartTime]-1-[EndTime],"Short Time"), which returns the same result (4:27) (just as a different approach to solving the issue)

    In the Detail section of my report I have a bound control ([Duration]), which displays what appears to correct data. In the Detail Section Footer I have a control using the formula =Sum([EndTime]-[StartTime]), which appears to return correct data.

    Now the problem… I want to total or sum the Sections (which are days) to arrive at the total time duration for each employee (for any time period specified by the user). I am using a control in the Employee Footer which uses =Sum([TotalTime]). This returns 1.2930555555556. The total time as I add up the detail sections (days) is 31:02 (31 hours and 2 minutes). Is this a format issue, or am I doing it all wrong?

    I have seen many many posts and info on calculating time duration but none of the examples I have tried so far have been fruitful. I think I understand that Access stores time as a point-in-time, and in order to calculate duration one needs to convert to a number format. Does that mean that I need to store the converted time in a number format, not Medium date as it is stored now… or, store both, or calculate such in the query, or on the form? If anyone can shed some light on this issue of calculating duration of time I would greatly appreciate it.

    Thanks…

    Larry

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Instead of =Sum([TotalTime]), I'm wondering if =Sum(Format([TotalTime],"00.00")) would work or (if I have it backwards - =Format(sum([TotalTime]),"00.00").
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Hello PK...

    The first returned an error, and the second {=Format(sum([TotalTime]),"00.00")} retuned 1:29.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    In computerese, 1.2930555555556 days = 31.0333333333344 hours, or 30 hours and 2 minutes.

    In Access, Excel, and who-knows-what-else, dates/times are stored as doubles. Dates are to the left of the decimal point, times under a day are to the right of the point.

    You can't handle the 'day' component with the 'time' component simultaneously. What you want to do is to handle it mathematically and concatenate the string values.

    Code:
    Dim dd as Integer, hh as integer, mm as integer, ITime as double, CTime as string
    
    ITime = 1.2930555555556  (of course, I mean the variable that holds that number)
    
    dd = Int(ITime)    '1
    hh = Hour(ITime)    '7
    Alternatively, if you don't want to use days + hours, but rather 31 hours, change the above to
    Code:
    hh = int(ITime * 24)    '31
    and ignore 'dd'
    Code:
    mm = minute(ITime)    '2
    
    CTime = dd & ":" & hh & ":" & mm
    which will read 1:7:2

    or

    Code:
    CTime = hh & ":" & mm
    which will read 31:2, if you used the second calculation for hh above.

    You can probably use the Format function to display it nicely, but I'm not positive about that.

    HTH,
    Sam

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    OK, I've come up with the display solution. A few tweaks to the code:

    Dim dd as string, hh as string, mm as string, ITime as double, CTime as string

    ITime = 1.2930555555556 (of course, I mean the variable that holds that number)

    dd = right("00" & Int(ITime), 2) '01
    hh = right("00" & Hour(ITime), 2) '07

    or

    hh = right("00" & int(ITime * 24), 2) '31
    mm = right("00" & minute(ITime), 2) '02

    CTime = format(dd & hh & mm, "00:00:00") '01:07:02

    or

    CTime = format(hh & mm, "00:00") '31:02

    Try it; it worked in my Immediate screen like a charm!

    Sam

  6. #6
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    OK... getting closer. I used: =Format(Sum([TotalTime]),"d:hh:nn") in the section footer control and it returned 31:07:02. I calculate manually to be 31:02:00.

    And Sam... I'm sorry but how do I make your suggestion work in my project? I have coded the following:

    Private Sub Report_Open(Cancel As Integer)

    Dim dd As String, hh As String, mm As String, ITime As Double, CTime As String
    ITime = [Duration]

    hh = right("00" & Int(ITime * 24), 2) '31
    mm = right("00" & Minute(ITime), 2) '02

    CTime = Format(hh & mm, "00:00") '31:02


    End Sub

    In the form control I placed: =[cTime]

    I get an debug error and hovering over iTime returns "0".
    Admittedly I struggle with VBA.

    Thanks...

    Larry

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    This may not be a solution but have you thought about actually storing the hours and minutes in fields? If you're doing a lot of calculations elsewhere, it might make it easier.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Larryg
    OK... getting closer. I used: =Format(Sum([TotalTime]),"d:hh:nn") in the section footer control and it returned 31:07:02. I calculate manually to be 31:02:00.

    And Sam... I'm sorry but how do I make your suggestion work in my project? I have coded the following:

    Private Sub Report_Open(Cancel As Integer)

    Dim dd As String, hh As String, mm As String, ITime As Double, CTime As String
    ITime = [Duration]

    hh = right("00" & Int(ITime * 24), 2) '31
    mm = right("00" & Minute(ITime), 2) '02

    CTime = Format(hh & mm, "00:00") '31:02


    End Sub

    In the form control I placed: =[cTime]

    I get an debug error and hovering over iTime returns "0".
    Admittedly I struggle with VBA.

    Thanks...

    Larry
    Larry,

    What variable type is Duration?

    Sam

  9. #9
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Sam... Duration is a calculated field on the QBE grid { Duration: Format([StartTime]-1-[EndTime],"Short Time") }. I have a control on the report with a Control Source of [Duration].

    PK... not a lot of calculations eleswhere... just this one report.

    Thanks guys for your help.

    Larry

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    There's the problem, Larry. Using the Format() function, as in

    Code:
    Duration: Format([StartTime]-1-[EndTime],"Short Time")
    changes whatever you have into text. Hence it's value is 0. Change Duration in the query as follows:

    Code:
    Duration: [EndTime] - [StartTime]
    IF and ONLY IF EndTime and StartTime are Date/Time or Double numeric variables. Otherwise, you need to use

    Code:
    Duration: CDate([EndTime]) - CDate([StartTime])
    in order to retain Duration as a date/time variable.

    Sam

  11. #11
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    OK... I solved the issue by using the Module and query/control expression found at this site: http://office.microsoft.com/en-us/ac...102181033.aspx

    Thanks Sam and PK for all your help.

    Larry

Posting Permissions

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