Split a delimited field to rows in query & join on Split fld

[Asher]

Hello,
I have 2 tables I need to join on data from a field that is string concatenated in T1 and delimited with " ;" <-- there is a space before the semi-colon.

I first tried using the Split function in the query, but that's not available in MS Access from the query, just from VBA - so I wrote a small wrapper function:

Code: Select all

Public Function String_Split(sInput As String, _
                      Optional sDelim As String = ";") As String
    If InStr(sInput, sDelim) Then
        'String_Split = split(sInput, sDelim)
    Else
        String_Split = sInput
    End If

End Function

but when I use it in a query like this:

Code: Select all

SELECT TOP 5,
Trim$(String_Split([T1].[Concatenated Item List],"  ; ")) AS SplitItem
FROM SearchTable1 AS T1

I get a Type mismatch (Error 13).

First I need to get that working right because THEN I need to use the SplitItem field to join with SearchTable2 which has a Item field.

The tables are as such:
SearchTable1:

IssueNum	Concatenated Item List
123	KK123 ;234 ;RR345
124	CC678

SearchTable2:

ActionNum	Item	IssueNum
2404	RR345	123
2405	234	123
2406	CC678	124

*Notice that Item KK123 has no ActionNum yet.
Expected result set: All Items parsed out with their respective IssueNum and (if it exists) the ActionNum, blank space if no ActionNum

Item	IssueNum	ActionNum
KK123	123
234	123	2405
RR345	123	2404
CC678	124	2406

Can someone help with this? I would accept a completely different approach to the problem...

Thanks

[HansV]

The VBA Split function returns an Array of Strings, not a String.

Can you explain what you want to do? I'm afraid that's not clear to me yet from your description.

[Asher]

Hi Hans,
I've edited the original question with the expected final result. But now that you say the Split() function returns an array of strings, it seems like I could maybe make a helper table to join my Action table with if I update my wrapper function to loop through the string array:

Code: Select all

Public Function String_Split(sInput As String, _
                      Optional sDelim As String = ";") As String
    Dim s, sa
    
    If InStr(sInput, sDelim) Then
        sa = split(sInput, sDelim)
        For Each s In sa
            String_Split = Trim$(s)
        Next
    Else
        String_Split = sInput
    End If

End Function

But how then, within that loop, do I create a new a row for each of the Items and pull in any other related fields to that row like the IssueNum field. Seems like there is a data object for row...

The helper table would look like this (except maybe with more fields):

Item	IssueNum
KK123	123
234	123
RR345	123
CC678	124

Is that the right approach? Or is there a way to do that in the original query using the returnd string array that would be less complex?

[HansV]

Should SearchTable1 remain as it is, or would it be OK to split the records in this table so that each record has a single value in the Concatenated Item List field?
Or do you prefer to create a new table for this?

[Asher]

I use SearchTable1 for other queries so having a separate updateable table would be nice. I reimport SearchTable1 an 2 every week.

[HansV]

Here is VBA code to create the helper table:

Code: Select all

Sub CreateHelper()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim i As Long
    Dim j As Long
    Dim a() As String
    On Error Resume Next
    DoCmd.DeleteObject ObjectType:=acTable, ObjectName:="HelperTable"
    On Error GoTo 0
    Set dbs = CurrentDb
    DoCmd.TransferDatabase DatabaseType:="Microsoft Access", DatabaseName:=dbs.Name, _
        Source:="SearchTable1", Destination:="HelperTable", StructureOnly:=True
    Set rstIn = dbs.OpenRecordset("SearchTable1", dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("HelperTable", dbOpenDynaset)
    Do While Not rstIn.EOF
        a = Split(rstIn![Concatenated Item List], " ;")
        For i = 0 To UBound(a)
            rstOut.AddNew
            For j = 0 To rstIn.Fields.Count - 1
                rstOut.Fields(j) = rstIn.Fields(j)
            Next j
            rstOut![Concatenated Item List] = a(i)
            rstOut.Update
        Next i
        rstIn.MoveNext
    Loop
    rstIn.Close
    rstOut.Close
End Sub

[Asher]

Hi Hans,
This code is awesome, however I am getting a invalid use of null error at:

Code: Select all

a = split(rstIn![Concatenated Item List], " ;")

This is probably because some of the results in the [Concatenated Item List] do have nothing or just 1 item (thus no delimiter).

So I tried to handle it with an if statement:

Code: Select all

        If IsNull(rstIn![Concatenated Item List]) Then
            ReDim a(1)
            a(0) = ""
        Else
            a = split(rstIn![Concatenated Item List], " ;")
        End If

But it's giving me duplicate fields where the data is null in the field.

In truth I don't need the rows where the data is null, just where there is one or more result in the field.

[HansV]

Try this:

Code: Select all

Sub CreateHelper()
    Dim dbs As DAO.Database
    Dim rstIn As DAO.Recordset
    Dim rstOut As DAO.Recordset
    Dim i As Long
    Dim j As Long
    Dim a() As String
    On Error Resume Next
    DoCmd.DeleteObject ObjectType:=acTable, ObjectName:="HelperTable"
    On Error GoTo 0
    Set dbs = CurrentDb
    DoCmd.TransferDatabase DatabaseType:="Microsoft Access", DatabaseName:=dbs.Name, _
        Source:="SearchTable1", Destination:="HelperTable", StructureOnly:=True
    Set rstIn = dbs.OpenRecordset("SearchTable1", dbOpenForwardOnly)
    Set rstOut = dbs.OpenRecordset("HelperTable", dbOpenDynaset)
    Do While Not rstIn.EOF
        If Not IsNull(rst![Concatenated Item List]) Then
            a = Split(rstIn![Concatenated Item List], " ;")
            For i = 0 To UBound(a)
                rstOut.AddNew
                For j = 0 To rstIn.Fields.Count - 1
                    rstOut.Fields(j) = rstIn.Fields(j)
                Next j
                rstOut![Concatenated Item List] = a(i)
                rstOut.Update
            Next i
        End If
        rstIn.MoveNext
    Loop
    rstIn.Close
    rstOut.Close
End Sub

[Asher]

Hans!
I have been having trouble logging on, but I finally got in and wanted to tell you that this worked! I need to study up on those DAO objects. They are powerful stuff! :-) Thanks!