Change data format

[chrisms]

Guys,

I have a table with a field in which the data is of the form:
0 Day(s) 0 Hr 2 Min which is formatted as a text string. I need to turn this into number values so that i can perform calculations on the data. How can I do this so I can run a query where I sum the values of the field, getting a result in number of minutes.

Thanks

Chris

 

[gblack]

Try this

Cut this to code into a new field in your query. Change the "day(s)", "hr" and "Min" fields if those aren't the proper names of the fields:

TotalMinutes: Cint([Day(s)]) * 1440 + Cint(

---

) * 60 + Cint([Min])

This code should add up the all the minutes. FYI: 1440 is 60*24 giving you the number of minutes in a day.

Hope this helps

 

[chrisms]

g,

Sorry may not have been clear initially. the data as posted is all in one field as a text string. What I need to do is work out a way of getting the digits out into seperate table.

For example: Currently - Duration = 0 Day(s) 1 Hr 40 Min

target is : DurDay = 0
DurHr = 1
DurMin = 40

Then I can count the etc. So how do I get the data out of the text string?

 

[gblack]

OK Try this:

I’d do it like this. I’ll call your field [Time].

DurDay: CInt(Mid([Time],1,InStr([Time],"D")-1))
DurHr: CInt(Mid([Time],(InStr([Time],")")+1),(InStr([Time],"H")-1)))
DurMin: CInt(Mid([Time],(Instrev([Time],"r")+1),(InStr([Time],"M")-1)))

That should get you what you need...dunno...you might have to Trim it.

 

[chrisms]

g,

That works pretty much perfectly. I changed the search to field to " " for DurHr and DurMin which worked well, as before generated an Error, however now I only get the first digit of the Minutes field, is it the new search to field or is it a virtue of the CInt thing, presuming that just declares the field as an Integer, so don't expect it to be that.

Thanks for the help

Chris

 

[chrisms]

g,

sorted that issue as well now, much thanks for the swift and accurate help!

Chris

 

[gblack]

Great!

Glad I could help!