If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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. -- |
#4
|
|||
|
|||
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] |
#5
|
|||
|
|||
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] |
#6
|
|||
|
|||
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] |
#7
|
|||
|
|||
"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!) |
#8
|
|||
|
|||
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. -- |
#9
|
|||
|
|||
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. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
how do I format numbers for time with thousandths of a second? | Mike Stoller | General Discussion | 2 | February 1st, 2005 04:19 AM |