Split string data into multiple columns access

问题:

I have a column with multiple service codes that I am looking to split into different columns for tallying purposes in Access. An example of one line looks like this:

$= ** ;= AA AC BB1 CA5 CC4 CC5 CC6 CC7 CC9 CD1 CI CR D0 D1 D2 D3 D9 F> F> FA7 FA9 FB1 HA6 KJ P* P? PW PZ QG V4 WJ 1D 2O

The goal is to create an AA column, an AC column etc to tally which service code is being used for what row. Also each line of service codes is uniquely different from the others. Any thoughts? I'm kinda familiar with macros and SQL queries too.

Here's what I have so far:

Sub splitColumn()

Dim rst As DAO.Recordset
Dim arr() As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("SELECT * FROM _Raw")
arr = split(rst("Serv Cde String"), ",")

For i = 0 To UBound(arr)
  '/do something here
Next i

End Sub

Thank you,

Rebecca

回答1:

The separator seems wrong:

Sub splitColumn()

    Dim rst As DAO.Recordset
    Dim arr As Variant
    Dim i As Integer

    Set rst = CurrentDb.OpenRecordset("SELECT * FROM _Raw")
    arr = Split(rst("Serv Cde String").Value, " ")

    For i = LBound(arr) To UBound(arr)
      '/do something here
      Debug.Print i, arr(i)
    Next

    Set rst = Nothing

End Sub