"Text to Columns" with Access

[spg2112]

Hey all,

I have a field in my table in the a format similiar to this:

SPGHR_IDR_HOUSTON_WHATEVER

I want to write a query that will split this up into 4 separate columns, like the Text to Column" feature in Excel.

What's the easiest way to do this?

Steve

[Norie]

Steve

There are various ways to do this.

How exactly is the data delimited? Is it by the underscore?

Is there always only 4 pieces of data?

If there is you could use a user defined function.

Perhaps something like this.

Function SplitData(DataField As String, Pos As Long, Optional Delimiter = "_") As String
Dim arrData
    arrData = Split(DataField, Delimiter)
    SplitData = arrData(Pos)
End Function

Which could be used as an expression in a query like this.

GetFirst:SplitData([YourField], 0)

Note the use of 0, this is because Split returns a 0 based array.

[spg2112]

Thanks Norie,

That worked great for the 1st column, I take it I need to write a similiar function for each column, and if so, how is Pos known?

Steve

[Norie]

Steve

What the code does is split the data delimiting by "_".

So for the 2nd column change 0 to 1, for the 3rd column change 0 to 2 etc

[spg2112]

Norie,

That works perfect! Thank you!

Steve