Query split text field into multiple fields separated by commas

Question

Hello,

I have a table with a text field for example 0413,shm,t1,z8.5,BS.

I would like to create an Access query to split the text into separate fields based on a comma separation.

So the result would look like this.

Field1      Field2      Field3      Field4      Field5

1803        shm         t1          z8.5        BSIf and how to achieve this in query. Thanks for any help.

Answer 1

Maybe some fields don't have five field parts.

Try this modification:

Public Function SplitField(ByVal Value As String, ByVal Index As Integer) As Variant

    If Ubound(Split(Value, ",")) >= Index - 1 Then

        SplitField = Split(Value, ",")(Index - 1)

    Else

        SplitField = Null

    End If

End Function

Answer 2

Create a small helper function:

Public Function SplitField(ByVal Value As String, ByVal Index As Integer) As Variant

    SplitField = Split(Value, ",")(Index - 1)

End Function

Now, use a query like this:

Select

    *, 

    SplitField([YourField], 1) As Field1,

    SplitField([YourField], 2) As Field2,

    SplitField([YourField], 3) As Field3,

    SplitField([YourField], 4) As Field4,

    SplitField([YourField], 5) As Field5

From

    YourTable

Answer 3

Split ("YourTextFieldName", ",")(0)

would give 0413

Split ("YourTextFieldName", ",")(1)

would give shm

and so on.

Answer 4

GustavBrock,

Thanks for your help. I tried your function and it error out saying.

"Run-time error '9':

Subscript out of range"

Debug pointing to

SplitField = Split(Value, ",") (Index - 1)

What did I do wrong?