Splitt field in new table and new records

[mfmuc]

Hi,
I would need a Access program that is doing following.

I have a table with following field:



Table: Input
ID Comment
4711 abc,bbc,xyz,bmw
4712 akw,ibm,

These records needs to be splitt into a new table

Table: Output
ID Value
4711 abc
4711 bbc
4711 xyz
4711 bmw
4712 akw
4712 ibm

Can someone please help me?

Best,
Martin

[aytee111]

Here is some code which uses the Split function (change names as applicable):

 

Public Function SplitTheField()
    Dim LString As String, LArray() As String, rst As Recordset, rstOut As Recordset, x As Integer
    On Error Resume Next
    Set rst = CurrentDb.OpenRecordset("Input_tbl", dbOpenDynaset)
    Set rstOut = CurrentDb.OpenRecordset("Output_tbl", dbOpenDynaset)
    DoCmd.RunSQL "DELETE * FROM Output_tbl;"
    rst.MoveFirst
    If Err <> 0 Then
        MsgBox "No input records found"
        Exit Function
    End If
    
    Do Until rst.EOF
        If Right(rst!InComment, 1) = "," Or Right(rst!InComment, 1) = " " Then
            LArray = Split(Left(rst!InComment, Len(rst!InComment) - 1), ",")
        Else
            LArray = Split(rst!InComment, ",")
        End If
        If Err <> 0 Then
            MsgBox "Invalid string format found, cannot process - " & rst!InId & " " & Err.Number & " " & Err.Description
            Err = 0
        Else
            For x = 0 To UBound(LArray)
                rstOut.AddNew
                rstOut!InId = rst!InId
                rstOut!OutComment = LArray(x)
                rstOut.Update
            Next
        End If
        rst.MoveNext
        Err = 0
    Loop
    Set rst = Nothing
    Set rstOut = Nothing
    
End Function

[mfmuc]

Thank you so much. This saves me more than one hour of work! Best, Martin

[aytee111]

You're welcome. I should probably have provided the tables!
Input_tbl:
InID number
InComment text

Output_tbl
OutID Autonumber
InId number
OutComment text