Need to sum the value of a field populated by code

[lwilly]

I have a report which takes the date and time an order is scanned to a department, as the "time in" and date and time that it is scanned to the next department as the "time out". Using the DateDiff function I get the amount of time an order was in a department. Unfortunately I can not Sum the total amount of time for each department. Can anyone tell me what I am missing?

 

[MichaelRed]

DateDiff returns a value of the "Intervals" tested. Thus:

DateDiff("d", "#3/18/01 10:00:00 AM#, #3/19/01 #11:59:59 PM#

returns the Long/Integer Value "1", so depending on the interval selection, you will only ever get some number of the intervals, so -AS LONG AS THE INTERVALS ARE THE SAME, you can simply add the various intervals. Interperting them as "Time" is somewhat more circutious. Depending on the "Interval", you need to subdivide the total to derive days/hours/minutes/seconds. this is generally approached by dividing the total by the value of the interval (e.g. If you have minutes as the overall interval, you could do an integer divide by 1440 to get the number of days. Then reduce the Sum of the interval by the number of days (* 1440), which gives the Number of days and the remainder of minutes. Divide the remainder by 60 and get the number of hours, with the remainder being minutes ...


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[lwilly]

I am getting the DateDiff in minutes for each order in the detail section of the report. In the "Departmentt" footer I am trying to get the sum of all of these minutes. This is where I come up short.

 

[MichaelRed]

Public Function basDbl2HrsMin(AccumTime As Double) As String

    Dim tmpDays As Double
    Dim tmpHrs As Double
    Dim tmpMins As Double
    Dim tmpTime As Double
    Dim tmpStr As String

    tmpDays = Int(AccumTime)
    tmpTime = (AccumTime - tmpDays) * 1440      'Fraction to Mins

    tmpMins = tmpTime Mod 60
    tmpTime = tmpTime - Int(tmpMins)
    tmpHrs = tmpTime \ 60

    'formatting for Day(s)
    If (tmpDays) Then
        tmpStr = tmpDays & "Day"
        If (tmpDays > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'formatting for Hour(s)
    If (tmpHrs) Then
        tmpStr = tmpStr & " " & tmpHrs & "Hr"
        If (tmpHrs > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    'formatting for Minutes(s)
    If (tmpMins) Then
        tmpStr = tmpStr & " " & tmpMins & "Min"
        If (tmpMins > 1) Then
            tmpStr = tmpStr & "s"
        End If
    End If

    basDbl2HrsMin = tmpStr

End Function

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over