Help needed with Split()

[moishy]

Hello all Experts,

I've inherited a .mdb with hundreds of thousands of records the problem is that there is no structure to the tables.
The issue I'm dealing with now is the following:
The table includes 5 columns, ID, Month1, Amount1, Month2, Amount2.
The problem is that each one of the Month an Amount fields have multiple records separated by the + sign.

Note: Not all the ID's have the same number of records in each column (in fact they very rarely do).

The question is how can I split each record to a separate field?

I've tried using this query

SELECT MyTable.ID, MyTable.Amount1, IIf(IsNull(Testsplit([Amount1],0)),"",Testsplit([Amount1],0)) AS Part1, IIf(IsNull(Testsplit([Amount1],1)),"",Testsplit([Amount1],1)) AS Part2, IIf(IsNull(Testsplit([Amount1],2)),"",Testsplit([Amount1],2)) AS Part3, IIf(IsNull(Testsplit([Amount1],3)),"",Testsplit([Amount1],3)) AS Part4, IIf(IsNull(Testsplit([Amount1],4)),"",Testsplit([Amount1],4)) AS Part5, IIf(IsNull(Testsplit([Amount1],5)),"",Testsplit([Amount1],5)) AS Part6, IIf(IsNull(Testsplit([Amount1],6)),"",Testsplit([Amount1],6)) AS Part7, IIf(IsNull(Testsplit([Amount1],7)),"",Testsplit([Amount1],7)) AS Part8, IIf(IsNull(Testsplit([Amount1],8)),"",Testsplit([Amount1],8)) AS Part9, IIf(IsNull(Testsplit([Amount1],9)),"",Testsplit([Amount1],9)) AS Part10
FROM MyTable;

The Testsplit function is

Function TestSplit(strInput As String, intNr As Integer)
    TestSplit = Split(strInput, "+")(intNr)
End Function

But I get Run-Time error '9' - Subscript out of range

What am I doing wrong? How can I accomplish the above?
Attached please find a sample with some data.

Your assistance is most appreciated.

p.s. I wasn't sure if this is the proper forum for this question since it involves vba, or if the query forum is more appropriate, since I'm using a query, yet I decided to post it here because I'd rather use pure vba that way I have all code in one place as apposed to using a function with a query.

 

[NickHa]

Where does the error occur?
It looks like your TestSplit function may be the culprit. Split returns an array of strings which represent the text between delimiters ("+" in your case). You need to define a string array of zero length (Dim strSplit () As String) and then use this forthe output of the split function. You would have to iterate the array in order to get the idividual elements, which cuts across the query structure. Different logic required here!
In TestSplit, what is the purpose of intNr? Your use doesn't fit the rules of Split, so I'd expect it to generate a complie-time error.

 

[stopher]

Here's the function you need (pretty much what NickHa was saying..)

Public Function TestSplit(strInput As String, intNr As Integer) As String

    Dim myArray() As String
    
    myArray = Split(strInput, "+")
    If intNr <= UBound(myArray) Then
        TestSplit = myArray(intNr)
    Else
        TestSplit = ""
    End If
    
    
End Function

The above function does all the work for you so you can simplify your query to:

SELECT MyTable.ID, MyTable.Amount1, Testsplit([Amount1],0) AS Part1, Testsplit([Amount1],1) AS Part2, Testsplit([Amount1],2) AS Part3, Testsplit([Amount1],3) AS Part4, Testsplit([Amount1],4) AS Part5, Testsplit([Amount1],5) AS Part6, Testsplit([Amount1],6) AS Part7, Testsplit([Amount1],7) AS Part8, Testsplit([Amount1],8) AS Part9, Testsplit([Amount1],9) AS Part10
FROM MyTable

In TestSplit, what is the purpose of intNr? Your use doesn't fit the rules of Split, so I'd expect it to generate a complie-time error.

The purpose of intHr is to return the element of the array with nidex intNr. As you know, split returns an array so SPLIT(....)(3) will return the element of the array with index 3.

Chris

 

[moishy]

stopher,

Thank you very much is does the job.

The only problem is that if any records are empty (no amount for id) that whole line is full of #error

 

[NickHa]

Yes, I know your error is run-time, but I thought the syntax of your split funtion would cause a compile-time error because intNr sat outside of the split. Chris spotted the intended use which I had missed and he's given you the code you need, so hopefully problem solved!