Converting text field (00:00:00) to a time field

[d.romanek]

Please help me. I am an intermediate Access user. I need to convert a text field containing a total amount of time (example: 128:15:52 -- 128 hour, 15 minutes, 52 seconds) as a time field. The data is imported into a database as a text field.

Again, please put in non-programming term so that I can understand instructions! Thank you!

[Robeen]

For the example that you gave: 128:15:52 . . . what do you want that converted to?

Where will you be using the converted data - will it be in a query . . . a Form . . . ?

A Time field [DateTime] usually just reports a point in time - for example 12/15/2012 9:27:00 [9:27 AM on Dec. 15th, 2012].
Your 128:15:52 is not a particular point in time - but a measurement of an amount of time.

Perhaps if you tell us exactly what you will do with the 128:15:52 . . . we will be able to assist.

[d.romanek]

I export data regarding employee stats for a call center. For a particular period of time, the running amount of time is what I want to convert. The exported fields come across as text fields. So one employee may have an accumulated call time for the week of 40:13:24 (40 hours, 13 minutes, & 24 seconds). For my Access report, I need to convert that text field time to an actual time field so I can calculate sums and averages when I run queries gathering several days or months worth of data.

[Beetle]

Well, you can't convert this to a time field because, as mentioned before, it's not a time value but a string of text that represents elapsed time. Elapsed time is actually a numeric value, not a time value, so you need to convert this to a number. In this case the lowest common denominator (so to speak) is seconds, so I would convert this value to total seconds with something like;

Left([ElapsedTime],InStr([ElapsedTime],":")-1)*3600+Mid([ElapsedTime],InStr([ElapsedTime],":")+1,2)*60+Right([ElapsedTime],2)

You could do this in a calculated query field, then sum that calculated field for all records. Once you have the total seconds for the entire record set you can convert that value back with;

Format(Int([TotalTime]/3600),"00") & ":" & Format(Int(([TotalTime]-(Int([TotalTime]/3600)*3600))/60),"00") & ":" & Format((([TotalTime] Mod 60)),"00")

Replace ElapsedTime and TotalTime in the above examples with your actual field names of course.