Recording Run Time

[Guest]

I am trying to record, in a table, the run time for various reports. I have
the StartTime and EndTime define as date fields. I want to post the
difference into a field in an access table. This formula gives the format
that I want when it is displayed in a message box, but won't record in like
manner in the Processing_Time field of my in the access table.

Here is the formula:

MsgBox ("Time Is " & Format((EndTime - StartTime), "h:m:s") & " Ended")

Returns (ie) : 4:10:30<---------This is what I want!

When I post this to a variant, date, or text formatted field the results are
different, usually something like:

12:00:00pm

Any Suggestions Please?

Thanks

Ross

 

[Tim Ferguson]

Here is the formula:

MsgBox ("Time Is " & Format((EndTime - StartTime), "h:m:s") & "
Ended")

When I post this to a variant, date, or text formatted field the
results are different, usually something like:

12:00:00pm

I don't really understand how the DateTime gets turned into this -- I
strongly suspect there is an integer truncation going on somewhere.

More to the point, though, is that Access DateTime values do not do well
with durations: any length of time over 24h will display as 31/12/1899
etc etc. You would be better off selecting some suitable time unit
(seconds or minutes etc.) and carrying out the conversion yourself:

rs!ReportDurationMinutes = (EndTime - StartTime) * 24 * 60


and then



Public Function MinutesToString(NumberOfMinutes As Double) As String

MinutesToString = Format(NumberOfMinutes \ 60, "0:") & _
Format(Int(NumberOfMinutes) Mod 60, "00:") & _
Format(Int((NumberOfMinutes - Int(NumberOfMinutes)) * 60), "00")

End Function




Hope that helps


Tim F

 

[Guest]

Tim,

I had no idea! Thank you, I give it a try.

Ross