Manipulate Data

[Zippyfrog]

Hello. Right now I have a table with the field "Time" in it, and it was set as a string. So some records have a time of 1:32 and others have a time of 5:30 etc. I want to split the field into two fields - Hours and Minutes.

I copied and pasted the entire Time field into the hour and minutes fields, so for the first record the values for Hour is 1:32 and minutes is 1:32.

For the Hour field, how can I delete everything after the colon? So that when it is done, it only shows a "1"?

Same thing for the Minute field - how can I delete everything before the colon, so that only the 32 would stay in the minute field?

I could go through and manually delete everything, but I would think there was some automated process I could use. Any suggestions?

-Chris

 

[jeremie_ingram]

You could use the query builder to make these changes, and could have used it to do the initial split. Say you have a table the has HOURS and MIN as the fileds you want to "TRIM" down either before the colon or after the colon.
If you were to add a calculated field such as
HRS: Left([Hours],InStr([HOURS],":")-1)

That would take the field HOURS then count the spaces until the : appears, subtract 1 (so as not to display the colon), then produce everything to the left of that point in a field called HRS (not HOURS)

To get the MINs
MIN2: Right([MIN],InStr([HOURS],":"))
This does the same except you dont have to subtract 1.

This is how you can use a query to change/manipulate that data in your tables. I have used it alot when it comes to seperating FIST and LAST names in a database.

 

[WayneRyan]

Chris,

You might also see the TimeSerial function in Help or on the
Search facility here.

Wayne