storing time information

[Guest]

Finally decided to create a database, for a sports club.

Sorted all other problems and errors so far, except for my "time" dilema.

The situation is that one competitor will have times for several events. The
times for these events will be measured in minutes:seconds:hundredths of
seconds, so a mask like ##:##.## (or ##:99.99).

However, if I enter 11:23.69 I get an error "The value you have entered is
not valid for this field" if I change the "69" to "59" its OK. Hmm..., from
what I've read so far, Access deals with date/time down to (whole) seconds
only.

Perhaps I should store time as text???

Any ideas?

thanks

 

[Ken Snell [MVP]]

Might be better to store the time as the total number of hundredths of a
second represented by that time -- as a long integer, perhaps. Then you can
manipulate the value into whatever display format you wish via a query.

Thus, you would store the time 2:11.69 as 13169 ( (((2 * 60) + 11) * 100) +
69 ).

 

[Guest]

Thanks Ken (that was quick).

Does this mean that Access dosn't natively handle untits of time smaller
than a second (eg: tenths of a second) as time? Pity if its true :-(

I was intending to use a form to show the all the times entered (like
2:11.69) for an individual apart from any reports. If I store as a long
integer (13169), then I suppose I can run a calculation to display them as
"readable times" in Forms and Reports. Would this be the way to go?

I was intending to use the time entered in the field (as 2:11.69) directly
in the associated form for display. That is, edtering and displaying in the
one form. I might need to have a separate entery and display forms and some
coding for each.

I think my little project just got harder... Although it sounds a better
long term method than storing as text (he says in a positive frame of mind).

 

[Ken Snell [MVP]]

ACCESS is designed for times of one-second increments with respect to
accuracy, yes. It actually stores time as a fraction of a 24-hour day...so
one second if time (actually, 12:00:01 am) is stored as
0.0000115740740740741.

I would suggest that you let users type the minutes into one textbox and the
seconds (with the hundredths) into another textbox. Or you could let the
user type them all together using nn:ss.ff format, and then convert that
entry (using parsing and calculations) into the hundredths of second.

 

[Jeff Boyce]

It sounds like you are trying to use the Access Date/Time datatype to store
"duration". It may look like it can do that, but it is design to store
"point-in-time" data. You can prove this to yourself by adding values that
total to more than 24 hours, then displaying the Time(YourField) function
value.

As Ken is pointing out, you can store the seconds (or hundredths) and build
parsing routines that display that in hh:nn:ss.dd format. If you use a
Currency data type (and leave off the $ formatting), you get 4 decimal
places -- store seconds and use decimal places for hundredths if you use
this approach.

Regards

Jeff Boyce
<Access MVP>