How can I store time values in access

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

Guest

I need to be able to enter and compare time that it takes to complete a lap
of a circuit for athletes. Time is measured in minutes, seconds and fraction
of seconds. I know access 2003 is limited and will only store time as
hh:nn:ss. One of my queries will be a report on the time difference from
what the athlete ran to what is the current track record and whole seconds is
not good enough.
Is there any other way that I can achieve this?
Many Thanks in anticipation
Allan
 
Allan said:
I need to be able to enter and compare time that it takes to complete
a lap of a circuit for athletes. Time is measured in minutes, seconds
and fraction of seconds. I know access 2003 is limited and will only
store time as hh:nn:ss. One of my queries will be a report on the
time difference from what the athlete ran to what is the current
track record and whole seconds is not good enough.
Is there any other way that I can achieve this?
Many Thanks in anticipation
Allan
Click to expand...

In addition to the resolution problem an Access DateTime is really only good for
storing a point in time, not an amount of time. I would provide two unbound
TextBoxes for entering the Minutes, and Seconds (decimals allowed in the latter)
and then use the BeforeUpdate event of the form to multiply the minutes by 60
and add that to the seconds storing the result in a Currency field in the table.
That will give you four decimal places of precision on the seconds.

Any comparisons and addition would be done on this TotalSeconds field and then
presented for display by converting it back to Minutes, Seconds, and fractions
of a second on your forms and reports.
 
Rick Brandt said:
In addition to the resolution problem an Access DateTime is really only good for
storing a point in time, not an amount of time. I would provide two unbound
TextBoxes for entering the Minutes, and Seconds (decimals allowed in the latter)
and then use the BeforeUpdate event of the form to multiply the minutes by 60
and add that to the seconds storing the result in a Currency field in the table.
That will give you four decimal places of precision on the seconds.

Any comparisons and addition would be done on this TotalSeconds field and then
presented for display by converting it back to Minutes, Seconds, and fractions
of a second on your forms and reports.
Click to expand...

Alternative, you could have a single unbound textbox, and let the user enter
mm:ss.dec in that box.

In the BeforeUpdate event, you could have a function that converts the text
time to a long integer representing total hundreds of seconds (or lower
resolution if you wanted), or a currency field like Rick suggests.

The largest value that can be stored in a Long Integer is 2,147,483,647. If
that represents hundreds of seconds, you can store a time of 357913:56.47,
or over 248 days. Even going to 4 decimal points, you can store a time of
3579:08.3647, or more than 2 days.
 
Back
Top