Access Code to parse a delimited string in a field in a table

Private Sub ParseText

    Dim strSQL as string
    Dim rsSource as DAO.Recordset, rsDest as DAO.Recordset
    Dim db as DAO.Database
    Dim strArray() as string, intLoop as integer

    'this is the table you want the data to go into
    strSQL = "SELECT RowID, [Parsed] FROM yourTable" 
    set db = Currentdb
    set rsDest = db.OpenRecordset(strSQL)

    strSQL = "SELECT ID, [ColumnName] FROM yourSourceTable " _
                & "WHERE [ColumnName] IS NOT NULL"
    set rsSource = db.openrecordset(strSQL)
    While not rsSource.EOF
         strArray() = split(rsSource![ColumnName], "_")
         For intLoop = lbound(strArray()) to UBound(strArray())
             rsDest.AddNew
             rsDest!RowID = rsSource!ID
             rsDest!Parsed = strArray(intLoop)
             rsDest.Update
         Next
         rsSource.MoveNext
    Wend

    rsSource.Close
    set rsSource = nothing
    rsDest.Close
    set rsDest = Nothing

End Sub

Select all Open in new window