Text to Columns

[TJVFree]

I have a table in my access database that has fields that I would like to separate like you would in MS excel Text to Columns.
I will need to do this twice a month, if I can automate this step that would be great also

Is there an easy what to do this?

Examples:
12547, Trainer, Chapter, 25

123451, Captain

145875, Coach, Page, Number,

 

[dhookom]

Are those values all in a single field? You can use the Split() function to parse out the individual values.

You might want to change your application so you aren't storing multiple values in a single field.


Duane
Hook'D on Access
MS Access MVP

 

[TJVFree]

Thanks Duane, I've never used the split function. Can you give me an example of what I need to do?

Fieldname: Split([Action Needed])

 

[dhookom]

It isn't clear how consistent your data is or isn't. Generically, you can use something like:

split("a,b,c,d,e",",")(3) = d
split("a,b,c,d,e",",")(0) = a
split(a,b,c,d,e",",")(5) = error generated

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

you may find about Split in your VBA help, or try this code:

Dim str As String
Dim ary() As String
Dim i As Integer

str = "145875, Coach, Page, Number"

ary = Split(str, ",")

For i = LBound(ary) To UBound(ary)
    Debug.Print Trim(ary(i))
Next i

You get i Immediate Window:
[tt]
145875
Coach
Page
Number
[/tt]

Have fun.

---- Andy