CODEWITHSUNDEEP
sqlms-accessvba
Peter
Peter

Reputation: 21

Parse a PIPE delimited field into seperate fields (MS ACCESS 2007-2013)

I have a database in MC Access 2017-2013 where the fields are stored as a PIPE delimited string. Now I want a way to parse these fields in a SELECT query in separate fields.

Here an example of my database and the PIPE delimited field: Database Example

The field contains 9 Pipes. So I want to separate the field in 10 fields.

Here an example of the Output: Output Example

I would be great, if someone could help me.
Thanks, Peter

Upvotes: 0

Views: 1301

Answers (1)

Andre
Andre

Reputation: 27634

You need a VBA function in a standard module to implement the splitting, e.g.

Public Function SplitPipes(str As String, i As Long) As String

    Dim arSplit As Variant

    arSplit = Split(str, "|")
    ' Check that arSplit has enough elements
    If i - 1 <= UBound(arSplit) Then
        ' Split creates a 0-based array, but it is easier to start with index 1 in the query
        SplitPipes = arSplit(i - 1)
    Else
        ' out of elements -> return empty string
        SplitPipes = ""
    End If

End Function

Then you can use this function for every single field like this:

SELECT SplitPipes([Strategic Group],1) AS Destination, 
       SplitPipes([Strategic Group],2) AS SE,
       ...
FROM yourTable;

Note that the function currently has no error handling whatsoever. Added :)

Upvotes: 3

Related Questions