Thousandths of a second

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
And are only accurate to one second.

Jamie.
Click to expand...

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]
 
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?
Click to expand...

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]
 
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>

John Vinson said:
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?
Click to expand...

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]
Click to expand...
 
John Vinson said:
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!
Click to expand...

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.
 
Agree with you John
Click to expand...

So why is a DATETIME column which is stored as a FLOAT deemed
unsuitable but a FLOAT column is?

If milliseconds is the smallest unit of measure and there are concerns
about accuracy rounding for FLOAT, why not DECIMAL(n,3)?

Thanks,
Jamie.

--
 
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.
 
Back
Top