Representing a time span in a database

  • Thread starter Thread starter James Minns
  • Start date Start date
J

James Minns

Hi all,
I have to represent a time span in my database; What would be the accepted
method for doing this?
If I use date/time, the user can't enter a value above 24 hours.

I have to enter and store numbers like 650:34:12 (hours minutes and seconds)

Ideas anyone?

Thanks, James
 
Hi all,
I have to represent a time span in my database; What would be the accepted
method for doing this?
If I use date/time, the user can't enter a value above 24 hours.

I have to enter and store numbers like 650:34:12 (hours minutes and seconds)

Ideas anyone?

Thanks, James
Click to expand...

Durations are best stored in a number field, NOT in a Date/Time field
(which is best suited for recording exact moments in time). You could
store the duration in a Long Integer count of seconds (2342052 in this
case); you can use an expression like

[Duration] \ 3600 & Format([Duration] \ 60 MOD 60, ":00") &
Format([Duration] MOD 60, ":00")

to display in hh:nn:ss format; and you can use three unbound textboxes
on a Form with some VBA code to enter and display the value.

John W. Vinson[MVP]
 
Decide on how you want to actually store the value (in this case, probably
seconds is good), and store the value in a long integer field. You then can
convert the seconds back to hours:minutes:seconds via query calculation.

As for user entry, I suggest you use three textboxes: one for hours, one for
minutes, one for seconds. Then let the form's BeforeUpdate event calculate
the number of seconds and write that value to the seconds field.
 
James Minns said:
Hi all,
I have to represent a time span in my database; What would be the accepted
method for doing this?
If I use date/time, the user can't enter a value above 24 hours.

I have to enter and store numbers like 650:34:12 (hours minutes and
seconds)
Click to expand...

Thanks, John and Ken!
I'll store the number of seconds, and input the values from 3 textboxes!

James
 
Back
Top