Clif:
Its important when undertaking date/time arithmetic to understand how Access
implements the date/time data type. It is in fact as a 64 bit floating point
number of which the integer part represents the days and the fractional part
the times of day. The number is an offset from 30 December 1899 00:00:00,
which is the origin for Access's date/time implementation. So when you
subtract one date/time value from another you'll get the result as the number
of days, in your case 2.42708 days. So far so good.
However, when you format that as days and hours you are not formatting the
time duration, but the date which is represented by the underlying number as
the date of the month (which is what "d" represents) and the hour of the day
on that date.
It happens that 2.42708 is the value which Access stores for the date/time
of 1st January 1900 10:15:00 (Enter ? Format(2.42708, "dd mmmm yyyy
hh:nn:ss") into the debug window to see this for yourself), so the reason you
are getting a result of 1d 10h is because the date is the first of the month.
I can't see how updating another date/time column with the result of the
computation is going to make any difference. The underlying value is still
2.42708, so formatting it as "d\d h\h" should still give you the same result.
Time durations are not date/time values. The latter are points in time.
Its for good reason that the functions in the links to which you've been
referred have been developed, and I'd strongly recommend that you use one of
them, as your current approach is not reliable and will only give a correct
result fortuitously in some cases. For what its worth here's an old function
of my own for computing elapsed time:
Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date, _
Optional blnShowDays As Boolean = False) As String
Const HOURSINDAY = 24
Dim lngHours As Long
Dim strMinutesSeconds As String
Dim strDaysHours As String
Dim dblDuration As Double
dblDuration = dtmTo - dtmFrom
'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")
' get minutes and seconds
strMinutesSeconds = Format(dblDuration, ":nn:ss")
If blnShowDays Then
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & _
" day" & IIf(lngHours \ HOURSINDAY <> 1, "s ", " ") & _
lngHours Mod HOURSINDAY
TimeDuration = strDaysHours & strMinutesSeconds
Else
TimeDuration = lngHours & strMinutesSeconds
End If
End Function
To get the difference between two date/time values expresses as days, hours,
minutes and seconds you'd call it like so:
TimeDuration([Sample Time], [Test Time], True)
Its a simple task to amend the function give the result in your desired
format of course:
Public Function TimeDuration( _
dtmFrom As Date, _
dtmTo As Date) As String
Const HOURSINDAY = 24
Dim lngHours As Long
Dim strDaysHours As String
Dim dblDuration As Double
dblDuration = dtmTo - dtmFrom
'get number of hours
lngHours = Int(dblDuration) * HOURSINDAY + _
Format(dblDuration, "h")
'get days and hours
strDaysHours = lngHours \ HOURSINDAY & "d " & _
lngHours Mod HOURSINDAY & "h"
TimeDuration = strDaysHours
End Function
which you'd call with:
TimeDuration([Sample Time], [Test Time])
Ken Sheridan
Stafford, England
"Clif McIrvin" wrote:
> Thanks for the links.
>
> I've discovered that if I use an update query to put the calculated
> value into a Date/Time field the "d\d h\h" format gives me the expected
> results.
>
> Guess I'll keep the links for future reference, and chalk this one up to
> one of those, "Gee, that's odd." moments.
>
> --
> Clif
>
> "Pete D." wrote in message
>
> > http://www.everythingaccess.com/tutorials.asp?ID=Calculating-Elapsed-Time
> >
> > or more precise
> > From Graham R Seach & Douglas J. Steele
> > http://www.accessmvp.com/djsteele/Diff2Dates.html
> > Copy code from WEB page into a modulecompile and save, make sure
> > module name isn't same as the function name.
> > In query grid
> > MyElapsedTimeField: Diff2Dates("dhns",[StartTestField],[EndTestField])
> >
> > "Clif McIrvin" wrote in message
> >
> >> I've got a table with two date/time values: [Sample Time] and [Test
> >> Time]
> >>
> >> In a query I'm trying to arrive at the elapsed time:
> >>
> >> Elapsed Time: [Test Time] - [Sample Time]
> >>
> >> The resulting column displays the correct value, ie: 2.42708.... but
> >> when
> >> I format it using "d\d h\h" I get "1d 10h" instead of the "2d 10h" I
> >> expected.
> >>
> >> Obviously I'm doing something wrong -- please get me pointed in the
> >> right
> >> direction!
> >>
> >> Thanks!
> >> --
> >> My environment is XP Pro SP2, Access 2003 SP3 w/Hotfix using DAO 3.6.
> >>
> >
> >
> >
>
>
>
> --
> Clif
>
>
> >> Stay informed about: Stumped on elspsed time calculation 
FAQ
Search
Profile
Private Messages
Log in
