Datatype for hours over than 24?...

[eilob]

Hi, I got a problem inserting data on a column,

The data is called Elapsed Time and contain values over 24 hours as:
162:58:42
03:27:55
164:19:51
00:28:10

If I set the field as Date/Time it wont accept values over 24 hours as 162:58:42, if I set it to number doesnt take it.

How can I insert the data so that will take the values?

I set up the column data type as text and it takes them the problem is I need to Sum the hours after and it wont be possible if is set up as text

Thanks in advance
Eilob

 

[Golom]

You can use a DateTime field that incorporates both date and time. The field will not actually store "162:58:42" for example as that value but you can do something like this

Dim TValues() as String
TValues = Split("162:58:42", ":")
myDateTime = TimeSerial ( TValues(0), TValues(1), TValues(2) )
[COLOR=black cyan]' myDateTime is now 01/05/1900 6:58:42 PM[/color]

and to get your numbers back

ElapsedTime = DateDiff("h", 0, myDateTime) & ":" & _
              DateDiff("n", 0, myDateTime) - _
              (DateDiff("h", 0, myDateTime) * 60) & ":" & _
              DateDiff("s", 0, myDateTime) - _
              (DateDiff("n", 0, myDateTime) * 60)
[COLOR=black cyan]' ElapsedTime is "162:58:42"[/color]

If that's too messy then just store "162:58:42" as a text string and extract the values with

Dim TValues() as String
TValues = Split("162:58:42", ":")
Hours   = TValues(0)
Minutes = TValues(1)
Seconds = TValues(2)

 

[PHV]

I'm with Golom as, unfortunatly, Jet SQL don't admit interval data type.

 

[eilob]

Thanks for that

Apologies about this, but where should I place this code?

 

[Golom]

Not knowing your application, I really can't say where it should go.

The first block of code in the above is what you would use to take a string like "162:58:42" from the user and convert it to a DateTime to be stored in a table. The second block of code is what you would use to pull the datetime field from the table and convert it back to your preferred format.

Most of this depends on what use you are making of the field. If it is just a display thing then the text field option is probably OK. If you want to do calculations or comparisons then I would go with the DateTime field option.

 

[eilob]

Hi Golom,

Instead of the actual value would this work specifying the actual field?

Dim TValues() as String
TValues = Split([Elapsed_Time], ":")
Hours = TValues(0)
Minutes = TValues(1)
Seconds = TValues(2)

Should I use a module for this??

Thanks

 

[Golom]

It would ... provided that [Elapsed_Time] is a text string.

Should I use a module for this??

Depends on what you want to do with the result. That fragment of code produces three values (Hours, Minutes, Seconds). If you put it in a Function or Sub, what do you want returned to the routine(s) that call it?

 

[eilob]

Thanks for your help!

 

[dhookom]

I would not use a date/time or text field to store a duration of time. IMHO you decide the time increment such as hours, minutes, seconds, etc and the granularity to create a numeric field that might or might not include decimals.

I reserve date/time fields for storing a point in time.

Duane
Hook'D on Access
MS Access MVP