need to convert text to time only

[baldrex]

hmm...

I have something similar to this but in my case this will not work. I have a text field storing the value in mins in this format 000100 representing 1 min.

I need to break this into a time reference 00:01:00 so that I can use it to calculate total time spent.

I have tried several different date/time functions but they all add the date, which I do not need.

question is: is there a way to convert this text into a time only field ?

i.e. Duration time/duration
005005 ... 00:50:05
000905 ... 00:09:05
001500 ... 00:15:00

total 01:14:10

thanks for you help!

[puppydogbuddy]

hmm...

I have something similar to this but in my case this will not work. I have a text field storing the value in mins in this format 000100 representing 1 min.

I need to break this into a time reference 00:01:00 so that I can use it to calculate total time spent.

I have tried several different date/time functions but they all add the date, which I do not need.

question is: is there a way to convert this text into a time only field ?

i.e. Duration time/duration
005005 ... 00:50:05
000905 ... 00:09:05
001500 ... 00:15:00

total 01:14:10

thanks for you help!

Try >>>>> TimeValue([YourDateTimeFie ld])

[Scott Price]

hmm...

I have something similar to this but in my case this will not work. I have a text field storing the value in mins in this format 000100 representing 1 min.

I need to break this into a time reference 00:01:00 so that I can use it to calculate total time spent.

I have tried several different date/time functions but they all add the date, which I do not need.

question is: is there a way to convert this text into a time only field ?

i.e. Duration time/duration
005005 ... 00:50:05
000905 ... 00:09:05
001500 ... 00:15:00

total 01:14:10

thanks for you help!

Probably the easiest way is to go into the table design view and change the input mask for your column Duration to Long Time. The mask will end up looking like this: 99:00:00\ >LL;0;_

This will also ensure that any future data entered in this field conforms to the same format also.

Regards,
Scott

[baldrex]

Ahh yes I agree with both of you in regards to changing the data type in the table, however, part of the problem is that I am working with a proprietary program and the DB I use is a read only copy of a live DB.

so I have to convert it in the query; either a straight query or an append query

I even resorted to trying to modify a built in function:

TOD: TimeSerial ((Hour(Left([duration],2), (Mid([duration],3,2), (Mid([duration],5,2))))

and get a type mismatch error...surely there is a way to accomplish this.

<edit>just to be sure I tried the timevalue again and it just shows an error in the column

[puppydogbuddy]

Ahh yes I agree with both of you in regards to changing the data type in the table, however, part of the problem is that I am working with a proprietary program and the DB I use is a read only copy of a live DB.

so I have to convert it in the query; either a straight query or an append query

I even resorted to trying to modify a built in function:

TOD: TimeSerial ((Hour(Left([duration],2), (Mid([duration],3,2), (Mid([duration],5,2))))

and get a type mismatch error...surely there is a way to accomplish this.

<edit>just to be sure I tried the timevalue again and it just shows an error in the column

Try this link and see if it helps (go to the section for computing elapsed time):

http://office.microsoft.com/en-us/access/HA011102181033.aspx

[missinglinq]

Converting 005005 to 00:50:05 is relatively easy!

format("005005" ,"00:00:00")

The complicated part is the adding! The problem is that neither 005005 nor 00:50:05 is really time! Time is a part of date. I think what you need to do is to parse out the data you already have, i.e. 005005, into seconds, add all these seconds together and then convert the results into hrs/mins/secs. Assuming that duration is the field that holds the data you start with

(left(duration, 2)*3600) + (mid(duration,3 ,2)*60) + right(duration, 2)

will give you the total seconds for each duration. You then total up all the seconds and put the total in a function to break it down into the hrs/mins/secs.

If this sounds right to you, I already have a function to do this.

Linq ;0)>

[Denburt]

Ling is correct yet try this It should tend to your needs and return an actual time.

I used [re] instead of your [duration] field name quicker for me to verify,

TimeSerial(Left ([re],2),Mid([re],3,2),Right([re],2))

The actual query I used was as such.

[code=vb]
SELECT Table1.re, TimeSerial(Left ([re],2),Mid([re],3,2),Right([re],2)) AS 12
FROM Table1;[/code]

[baldrex]

awesome!!!

Thank you very much for all the help!

You guys are great!

[Denburt]

Glad to help the string you were using was real close if you compare the two I think you will see the difference.

[missinglinq]

Here's the function to convert seconds into hours/minutes/seconds HH:MM:SS.

In the Objects Dialog box click on Modules
Click on New
Paste this code in
[CODE=vb]Public Function TimeParsing(Tot alSeconds As Long) As String
'Parses seconds into Hours-Minutes-Seconds in HH:MM:SS format
Dim HoursLapsed, SecondsLeft, MinutesLapsed, SecondsLapsed As Long

HoursLapsed = Int(TotalSecond s / 3600)
SecondsLeft = TotalSeconds Mod 3600

MinutesLapsed = Int(SecondsLeft / 60)
SecondsLapsed = SecondsLeft Mod 60

TimeParsing = Format(HoursLap sed, "00") & ":" & Format(MinutesL apsed, "00") & ":" & Format(SecondsL apsed, "00")

End Function
[/CODE]Save the module and when prompted, name it TimeConversion.

To use the function do something like this

[CODE=vb]YourTimeTotalsF ield = TimeParsing(Tim eInSeconds)[/CODE]

where YourTimeTotalsF ield is the field to hold the parsed string (in hh:mm:ss format) and TimeInSeconds is your field holding the total of all seconds.

Linq ;0)>

[baldrex]

Thank you Linq, this works great!

[missinglinq]

Glad we could help!

Linq ;0)>

[Silvia A]

This is such an old post, but wanted to thank you becaue i have been searching online for days and this was the solution that worked for me. Thanks!

[NeoPa]

We understand Silvia, and we're always happy for old threads to be dug up to drop in a "Thank you". It's always welcome and reminds us that old threads are still helpful to people, even if the original contributors to the thread don't need it any more :-)