Thousandths of a second

[science]

Hi there,

I'm trying to put a field for an elapsed time into a table, for track
cycling results. I need to keep times in an hh:mm:ss.000 format, or at least
be able to view them as such. I'm using Access 2000. I can get the format to
work down to seconds, i.e. hh:mm:ss, but as soon as I put a decimal place in
I start getting error messages. I've tried trying to specify that it's an
elapsed time by using [h]:mm:ss, but it is taking this literally, and
displaying [1]:00:00. Is there a way to do this? Would upgrading to Access
2003 help?

 

[Ken Snell [MVP]]

Store the elapsed times using the smallest unit that you need to have
absolutely accurately, so long as you can fit it within the Long Integer
data type (if it won't fit, then split into separate fields: one for seconds
and fractions of seconds, again stored as whole number; then other amounts
as minutes (hours and minutes converted to minutes)). Then convert these
numbers to the desired display format as needed.

Date/time fields in ACCESS are designed to store a point in time, not an
elapsed time.

 

[John Vinson]

And are only accurate to one second.

Jamie.

actually, precise to a better than a millisecond - it's a Double Float
count of days and fractions of a day since midnight, December 30,
1899. As of this moment 3319699803 seconds have elapsed since then -
and since a Double is accurate to some 13-14 decimals, there's *room*
for more precision.

But you're quite correct of course in that there seems to be no way to
*display* or use that precision. Unfortunate!

John W. Vinson[MVP]

 

[John Vinson]

Hi there,

I'm trying to put a field for an elapsed time into a table, for track
cycling results. I need to keep times in an hh:mm:ss.000 format, or at least
be able to view them as such. I'm using Access 2000. I can get the format to
work down to seconds, i.e. hh:mm:ss, but as soon as I put a decimal place in
I start getting error messages. I've tried trying to specify that it's an
elapsed time by using [h]:mm:ss, but it is taking this literally, and
displaying [1]:00:00. Is there a way to do this? Would upgrading to Access
2003 help?

As an alternative to Ken's suggestion, you might want to store the
elapsed time in a Double Float number, a count of seconds (with
fractions of course). You'ld store 1:01:01.234 as 3661.234.

This can be displayed in hh:nn:ss.sss format using an expression like

[dur] \ 3600 & Format([dur] \ 60 MOD 60, ":00") & Format([dur] -
60*([dur] \ 60), ":00.000")

John W. Vinson[MVP]

 

[Ken Snell [MVP]]

Agree with you John... but the only reason I didn't go this way was the
possible concern about "floating point" numbers not being exactly accurate
for fractional numbers, though probably that accuracy would be at a smaller
dimension than thousandths of a second.

--

Ken Snell
<MS ACCESS MVP>

Hi there,

I'm trying to put a field for an elapsed time into a table, for track
cycling results. I need to keep times in an hh:mm:ss.000 format, or at
least
be able to view them as such. I'm using Access 2000. I can get the format
to
work down to seconds, i.e. hh:mm:ss, but as soon as I put a decimal place
in
I start getting error messages. I've tried trying to specify that it's an
elapsed time by using [h]:mm:ss, but it is taking this literally, and
displaying [1]:00:00. Is there a way to do this? Would upgrading to Access
2003 help?

As an alternative to Ken's suggestion, you might want to store the
elapsed time in a Double Float number, a count of seconds (with
fractions of course). You'ld store 1:01:01.234 as 3661.234.

This can be displayed in hh:nn:ss.sss format using an expression like

[dur] \ 3600 & Format([dur] \ 60 MOD 60, ":00") & Format([dur] -
60*([dur] \ 60), ":00.000")

John W. Vinson[MVP]

 

[Douglas J. Steele]

actually, precise to a better than a millisecond - it's a Double Float
count of days and fractions of a day since midnight, December 30,
1899. As of this moment 3319699803 seconds have elapsed since then -
and since a Double is accurate to some 13-14 decimals, there's *room*
for more precision.

But you're quite correct of course in that there seems to be no way to
*display* or use that precision. Unfortunate!

Actually, there is, John, although it's not guaranteed to be 100% accurate.

If you know that your time field contains smaller than seconds, you can
subtract the date/time in seconds from it, and work with the remainder.

 

[Ken Snell [MVP]]

I haven't used DECIMAL format in any database, but others have reported some
flakiness with it, so I generally shy away from recommending its use.

Currency can be used for similar purpose, however, as it maintains
"nonfloating-point" nature for 4 decimal places.