Difficulty splitting text string into multiple columns

[a2z]

I'm putting together an access database to be used as a reporting function for an online registration database. The online database exports its data into a csv which is then imported into MS Access.

I have four fields that have from 0 to 5 strings of text separated by commas. I need to separate those fields into upto five other fields.

I have created a module with the following code in it to take advantage of the "split" function:

Public Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As String
    SplitField = Split(strValue, strDelimiter, , vbTextCompare)(intPartWanted - 1)
End Function

Then in my query to create the five new fields, I have:

Class1: SplitField([Elective Classes - 2nd Session],",",1)
Class2: SplitField([Elective Classes - 2nd Session],",",2)
Class3: SplitField([Elective Classes - 2nd Session],",",3)
Class4: SplitField([Elective Classes - 2nd Session],",",4)
Class5: IIf([class4] Like "*,*",SplitField([Elective Classes - 2nd Session],",",5),Null)

When run I get the error:

Run-time error '9'
Subscript out of range

But if I just click on "End" enough times, it does eventually complete. Empty fields have "#Error" in them and where data should be, it does show up.

I am very new to Modules so likely it is the code there, but not 100% sure. Any help would be gratefully appreciated.

 

[MarkK]

The subscript problem is that one for your csv strings might have only two elements, but your SQL and VBA explicitly try to reference a third, fourth, and fifth.

In respect to database table design, the problem is that the data is stored incorrectly. I would create a table and separate out your csv values properly normalized, and then write a crosstab query to achieve what you are trying to do here, because the other problem--even if you were to solve the immediate error--is that this approach will be incredibly slow, because you are calling an external function five times per row.

hth
Mark

 

[a2z]

Thank you Markk! I agree that the data is not stored as well as could be, but it is what it is. This export/import then report process is something that has to be done periodically, not just a one in done and it will need to be done by administrative staff that will not have the time nor the inclination to normalize the data prior to running reports. So I need a way that can take the string, split it into multiple fields and go on. There will never be more than 300 records that have to be split so time is really not that big of a consideration. I believe I am stuck with getting this method to work, but I am open to suggestion if I'm going down the wrong path.

 

[arnelgp]

modify the function so it will handle that case

Public Function SplitField(strValue As String, strDelimiter As String, intPartWanted As Integer) As variant
   dim arrResult
    arrResult= Split(strValue, strDelimiter, , vbTextCompare)
    if ubound(arrResult)< (intPartWanted) then
        SplitField = arrResult(intPartWanted - 1)
    end if
End Function

 

[a2z]

Thank you arnelgp. I tried your code but it still throws that error and shows only part of the data when it finally completes. It

 

[arnelgp]

futher refine the function

Public Function SplitField(strValue As Variant, strDelimiter As String, intPartWanted As Integer) As variant
   dim arrResult
   strValue = strValue & ""
   If Instr(strValue, strDelimiter) <> 0 Then
    arrResult= Split(strValue, strDelimiter, , vbTextCompare)
    if (intPartWanted - 1) <= ubound(arrResult) then
        SplitField = arrResult(intPartWanted - 1)
    end if
    End If
End Function

 

[June7]

Try:

If UBound(arrResult) >= intPartWanted - 1 Then

 

[arnelgp]

that is good we are making progress

Public Function SplitField(strValue As Variant, strDelimiter As String, intPartWanted As Integer) As variant
   dim arrResult
   strValue = strValue & ""
   If Instr(strValue, strDelimiter) <> 0 Then
    arrResult= Split(strValue, strDelimiter, , vbTextCompare)
    if (intPartWanted - 1) <= ubound(arrResult) then
        SplitField = Trim(arrResult(intPartWanted - 1))
    end if
    Else If Len(strValue) <> 0 And intPartWanted = 1 Then
        SplitField = Trim(strValue)
    End If
End Function

 

[a2z]

arnelgp thank you again! I think we are getting closer. The errors have disappeared and a field that had three strings split out ok, but a field that only had one entry didn't show up at all in the results.

 

[a2z]

June7, thank you for chiming in. I tried your suggestion, but it didnt seem to change anything. Should I leave it like that or revert back to the code from arnelgp?

 

[arnelgp]

Chk post #8 that is the latest..

 

[MarkK]

You are going down the wrong path IMO. Store the data correctly, even if temporarily. This is the most major error people new to databases make, is to underestimate the difficulty of working with data that is not normalized. Look at the trouble you are going through. I would create a correctly structured temp table, write one VBA loop to store the data correctly, write one SQL query to retrieve the data correctly.

Instead, you are calling the same VBA function five times per row, and each call splits ALL your data, cherry-picks one element, and then does it again for the next field, splits ALL your data again, cherry-picks the next element, and so on, even if the last element didn't exist, the next field will still call the function. This is almost certainly the least efficient way to solve this problem.

hth
Mark

 

[a2z]

arnelgp thanks. I'm getting a syntax error on line

Else If Len(strValue) <> 0 And intPartWanted = 1 Then

 

[arnelgp]

sorry i have no computer only on my fon.
i think ElseIf is ine word

Public Function SplitField(strValue As Variant, strDelimiter As String, intPartWanted As Integer) As variant
   dim arrResult
   strValue = strValue & ""
   If Instr(strValue, strDelimiter) <> 0 Then
    arrResult= Split(strValue, strDelimiter, , vbTextCompare)
    if (intPartWanted - 1) <= ubound(arrResult) then
        SplitField = Trim(arrResult(intPartWanted - 1))
    end if
    ElseIf Len(strValue) <> 0 And intPartWanted = 1 Then
        SplitField = Trim(strValue)
    End If
End Function

 

[a2z]

Thank You arnelgp! You are the man! Thank you too to Markk and June7! Markk I hear what you are saying and will also look in to going down that path.

 

[arnelgp]

Dont wirry if yiu have I class computer and yiu are not splitting 1g of text of 1g record.