Help with custom use of Split()

[moishy101]

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, 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

The Testsplit function is:

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
 

Now this usually works well, but if any records are empty (no amount for the ID) that whole line is full of #error instead of not showing anything (or "")

Your assistance is most appreciated.

[NeoPa]

Wow. You know how to impress. This is a much better question (from Custom Split Help) and a pleasure to work with :-) Normally we don't allow the same question to be asked twice, but in view of the difference between the two, and that you were following instructions to the best of your understanding, instead of merging them and ending up with a confused thread, I'll close that one and leave it just for reference and allow this one to stand.

On to the problem, which is that strInput is defined as a String. This is fine if MyTable.Amount1 is a string, but for empty records it's likely to be Null (Blanks in fields are generally stored as Null rather than empty strings, though this can be controlled in the field setup.). As such, it's better to declare the parameter as a Variant rather than a String, and then handle the possibility in line #4 as follows :

Public Function TestSplit(varInput As Variant, intNr As Integer) As String
    Dim myArray() As String
 
    myArray = Split(Nz(varInput, ""), "+")
    TestSplit = ""
    If intNr <= UBound(myArray) Then TestSplit = myArray(intNr)
End Function

Line #5 is optional as TestSplit, as a string value, is automatically initialised to an empty string ("") anyway.

Let us know if this resolves your problem :-)

[moishy101]

It most definitely solves the problem. Thank you!!! :-)
Just to verify: what data type in stored in the array, and will that have any effect on writing the data to a table?

[NeoPa]

My pleasure :-)

The array is defined on line #2 and is of type String. As such "Yes".

Depending on the setup of your table, it may (most do) require empty values to be stored as Nulls. This can be handled by not setting the value at all if the string value is empty (""), or alternatively, using an array of Variants and maintaining the Null value as passed.