Thousandths of a second

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

Ken Snell
MS ACCESS MVP



"science" wrote in message
...
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] wrote:
Date/time fields in ACCESS are designed to store a point in time

And are only accurate to one second.

Jamie.

--

On 11 Mar 2005 08:08:53 -0800, "Jamie Collins"
wrote:


Ken Snell [MVP] wrote:
Date/time fields in ACCESS are designed to store a point in time

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]

On Fri, 11 Mar 2005 05:29:01 -0800, "science"
wrote:

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]

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" wrote in message
...
On Fri, 11 Mar 2005 05:29:01 -0800, "science"
wrote:

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]

"John Vinson" wrote in message
news
On 11 Mar 2005 08:08:53 -0800, "Jamie Collins"
wrote:


Ken Snell [MVP] wrote:
Date/time fields in ACCESS are designed to store a point in time

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!

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Ken Snell [MVP] wrote:
you might want to store the
elapsed time in a Double Float number, a count of seconds (with
fractions of course).

Agree with you John

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

You'ld store 1:01:01.234 as 3661.234.

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.

--

Ken Snell
MS ACCESS MVP


"Jamie Collins" wrote in message
oups.com...
Ken Snell [MVP] wrote:
you might want to store the
elapsed time in a Double Float number, a count of seconds (with
fractions of course).

Agree with you John

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

You'ld store 1:01:01.234 as 3661.234.

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

Thanks,
Jamie.

--