in access how do I set up a time field to accept 1/10000 sec

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

Guest

I need to set a table field to accept time in minutes, seconds and 1/10000 of
a second. How do I go about this and if possible do calculations on this
field. Example 1:23.4567
 
DateTime data type in Access/Jet won't do in this case.

I think you will need to use a Long Field storing the number of 1/10000 of a
secs and then manipulate the long value to display as Mins / Secs / Fraction
of sec. For example:
701234 = 70.1234 secs

and display (as Text):

1:10.1234
 
I need to set a table field to accept time in minutes, seconds and 1/10000 of
a second. How do I go about this and if possible do calculations on this
field. Example 1:23.4567
Click to expand...

I'd use, oddly enough, a Currency datatype for this. Date/Time fields
won't work: they don't let you display anything finer than one-second
accuracy, and aren't really suitable for durations anyway.

Currency is a scaled huge integer with four (and exactly four) decimal
places, and no roundoff error. Your example would be stored as
seconds: 83.4567. You can display it as minutes and seconds using an
expression like

[duration] \ 60 & Format([duration] - 60*([duration] \ 60),
":00.0000")

A Double Float datatype would be another possibility, but runs the
risk that calculated durations (sums, say) might differ in the
thirteenth or fourteenth decimal place, and that one 1:23.4567 might
be seen as being *unequal* to another 1:23.4567, because one is being
stored as 83.4566999999993 and the other as 83.45670000000002.

John W. Vinson [MVP]
 
I need to set a table field to accept time in minutes, seconds and 1/10000 of
a second. How do I go about this and if possible do calculations on this
field. Example 1:23.4567
Click to expand...

I'd use, oddly enough, aCurrencydatatype for this.

Currencyis a scaled huge integer with four (and exactly four)decimal
places, and no roundoff error. Your example would be stored as
seconds: 83.4567. You can display it as minutes and seconds using an
expression like

[duration] \ 60 & Format([duration] - 60*([duration] \ 60),
":00.0000")

A Double Float datatype would be another possibility, but runs the
risk that calculated durations (sums, say) might differ in the
thirteenth or fourteenthdecimalplace, and that one 1:23.4567 might
be seen as being *unequal* to another 1:23.4567, because one is being
stored as 83.4566999999993 and the other as 83.45670000000002.
Click to expand...

The 'decimal' portion of CURRENCY is adequate (barely <g>) but what
about the 'integer' portion (noting you admitted it is 'huge')? You
could a value of approx

? 922337203685477 / 60 / 60 / 24 / 365.25
29195129.5411734

years into such a column; allow it and one day you'll get such a value
<g>. Is banker's rounding appropriate? Would it be confusing to use a
financial data type for temporal data (noting you admitted it was an
odd choice)? And what would you have recommended for 1/100000 second
accuracy?

I'd recommend DECIMAL(n, 4) or possibly DECIMAL(n+1, 5) if there was
scope to store an extra decimal place to perform custom rounding
(DECIMAL truncates by nature). Limiting the integer portion to
reasonable limits is something you get for free with DECIMAL.

Jamie.

--
 
Back
Top