Representing a time span in a database

[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

 

[John Vinson]

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

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]

 

[Ken Snell \(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]

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)

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

James