Times

[Dennis Z]

I am making a database for a triathlon and was wondering if there was a way
to have access treat the times that are entered as times but not in the am/pm
format and nothing to do with dates, for example the person ran the first
part of the race in 20 minutes 34.33 seconds and then the second part in 18
Minutes 21.22 seconds. I need to be able to add and subtract these times and
was having troubles

 

[Larry Linson]

Enter the minutes, seconds, and fractions-of-seconds in separate text boxes.
You can then convert them to a
minutes-and-decimal-fractions-of-minutes-floating-point format for
calculating or storing them (but if you want to display in nn ss.ff format,
you'll have to reconvert).

You are correct that date/time data is a representation of a point-in-time,
not a quantity of time. (For example, if you use date time and only enter a
time, it really represents 12/30/1899 at the time you entered... 30 Dec 1899
being the date represented by a zero date... it is stored as a double
precision floating point variant number with the whole number part
representing days before or after 30 Dec 1899 and the fractional part
representing time since midnight of that date; sounds confusing, but the
translations are handled internally and it's not nearly as confusing as it
might seem).

Larry Linson
Microsoft Office Access MVP

 

[John W. Vinson]

On Mon, 9 Jun 2008 21:05:00 -0700, Dennis Z <Dennis

I am making a database for a triathlon and was wondering if there was a way
to have access treat the times that are entered as times but not in the am/pm
format and nothing to do with dates, for example the person ran the first
part of the race in 20 minutes 34.33 seconds and then the second part in 18
Minutes 21.22 seconds. I need to be able to add and subtract these times and
was having troubles

Date/Time fields are really intended for storing specific points in time - NOT
durations. Adding, averaging and subtracting date/time values will *often*
work but can give very strange results (e.g. if the sum exceeds 24 hours it
goes to the next day!) I'd store the racetime as a Double number, 1234.33
(seconds).

This can be displayed in nn:ss.ss format with an expression like

[runtime] \ 60 & " minutes " & Format([runtime] - 60*([runtime] \ 60),
"00.00")

and entered using two unbound textboxes for the minutes and seconds.