Split Function and resultant 1D array

[JustJim]

G'day everybody,

One of my clients' suppliers sent them a spreadsheet containing price increase data. For Ghu only knows what reason, they have a column with my client's part number and the supplier's description in the same cell.

No problem thinks I, I'll just suck it into Access as a table, Split that field in each record and put the bits where they belong. As below:

[CODE=vb]Private Sub btnFix_Click()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim strInput As String
Dim varInput As Variant

Set db = CurrentDb()
strSQL = "SELECT GoldacrePartNum ber, GasonDescriptio n from GasonNew"
Set rs = db.OpenRecordse t(strSQL, dbOpenDynaset)

Do Until rs.EOF
strInput = rs!GoldacrePart number & " "
varInput = Split(strInput, " ", 2)
rs.Edit
rs!GoldacrePart number = varInput(0)
rs!GasonDescrip tion = RTrim(varInput( 1))
rs.Update

rs.MoveNext
Loop

CleanUpAndLeave :
rs.Close
Set rs = Nothing
Set db = Nothing
DoCmd.Hourglass False
DoCmd.SetWarnin gs True
Exit Sub

End Sub[/CODE]
Well that worked fine except for the record which had no description appended, just the part number (and therefore no spaces to delimit the split). This caused varInput(1) to error with "Subscript out of range". So I added the & " " in line 13 and took the space back out in line 17

This strikes me as terribly inelegant and I was wondering if anyone might have a better solution.

There is absolutely no timeline here because the inelegant solution worked and everybody is happy except for nit-picky me.

Jim

[NeoPa]

I wouldn't use the Limit parameter to the Split() function Jim. Without that, there are no spaces in any of the elements returned.

[FishVal]

Hello, Jim.

Actually, I couldn't say it is inelegant solution compared to possible alternatives: using UBound() or ignoring the error. All of them look more or less similar in terms of elegance. :)

However, IMHO, a more elegant (and maybe more effective) solution is to use SQL/VBA combination.

UPDATE 
GasonNew 
SET 
GoldacrePartNumber = GetSubString(GoldacrePartNumber, ' ', 0), 
GasonDescription = GetSubString(GoldacrePartNumber, ' ', 1);

Public Function GetSubString(varInput As Variant, _
                        strDelimiter As String, _
                        intSubStringOrdinal As Integer) As Variant

    On Error Resume Next
    GetSubString = Split(varInput, strDelimiter)(intSubStringOrdinal)
    
End Function

[JustJim]

I wouldn't use the Limit parameter to the Split() function Jim. Without that, there are no spaces in any of the elements returned.

That's true Neo, but then the Split would return as many elements as there are delimiters - 1. ie if the initial string was "P/N1234 Big End Cog" I'd get four elements and I could put one into the part number field and have to re-concatenate the other three for the description field. Since I couldn't be sure just how many elements I would get, re-building the description field would be a loop from 1 to Ubound(varInput ).

Your thoughts?

[JustJim]

Hi Fish,

However, IMHO, a more elegant (and maybe more effective) solution is to use SQL/VBA combination.

GetSubString = Split(varInput, strDelimiter)(intSubStringOrdinal)

Isn't this only going to get me the single element referenced by intSubStringOrd inal, If I use the same example as above, the initial field being "S/N1234 Big End Cog" and I send a 1 as intSubStringOrd inal, I'm only going to get "Big". Is that not the case? Then I'd have to keep sending incrementing intSubStringOrd inal s until I got the Subscript out of Range error and trap that.

Maybe I stumbled on to the tidiest solution in my haste?

Your thoughts, as with Neo's are always welcome.

Jim

[NeoPa]

Ah. So they've mixed the data together using a character (' ') which also occurs naturally within the data. I probably could have worked this out for myself if I'd considered the likely scenario, but I was more focused on the conundrum tbh.

In that case, the problem is a bit more complicated and frankly, your solution is as elegant as could be expected.

Another quite valid method would be to use InStr() to find the position of the first space. The two substrings can be worked out quite easily from there, without the need to add to the original string.

Whichever method you're happiest with, can be used within your original code structure, or just as straightforward ly, within a SQL UPDATE structure as Fish suggests.

[FishVal]

Jim, you could implement whatever you like logic in VBA function(s).
e.g. using InStr() function to determine first delimiter occurrence and split string into two parts using Left(), Right() or Mid() function(s).

My point is that in most cases recordset iteration may be replaced with SQL. Result will be the same - some action performed on all records in dataset.

Regards,
Fish.

[JustJim]

Thank you both for your time and expertise. We'll call that one solved shall we?

Jim

[NeoPa]

What are your thoughts after all that Jim?

[JustJim]

What are your thoughts after all that Jim?

Thoughts:

If you're doing a quick and dirty job, a quick and dirty solution that works is acceptable.

There's more to SQL data manipulation language than SELECT... FROM...WHERE... Learn to consider where other SQL statements may be appropriate.

Elegance is, while not a luxury, not a necessity either.

Don't sweat the small stuff... and it's all small stuff until they start shooting at you.