By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,657 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,657 IT Pros & Developers. It's quick & easy.

Split Function and resultant 1D array

JustJim
Expert 100+
P: 407
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnFix_Click()
  2. Dim db As Database
  3. Dim rs As Recordset
  4. Dim strSQL As String
  5. Dim strInput As String
  6. Dim varInput As Variant
  7.  
  8. Set db = CurrentDb()
  9. strSQL = "SELECT GoldacrePartNumber, GasonDescription from GasonNew"
  10. Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
  11.  
  12. Do Until rs.EOF
  13.     strInput = rs!GoldacrePartnumber & " "
  14.     varInput = Split(strInput, " ", 2)
  15.     rs.Edit
  16.     rs!GoldacrePartnumber = varInput(0)
  17.     rs!GasonDescription = RTrim(varInput(1))
  18.     rs.Update
  19.  
  20. rs.MoveNext
  21. Loop
  22.  
  23. CleanUpAndLeave:
  24. rs.Close
  25. Set rs = Nothing
  26. Set db = Nothing
  27. DoCmd.Hourglass False
  28. DoCmd.SetWarnings True
  29. Exit Sub
  30.  
  31. End Sub
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
Apr 27 '09 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,712
I wouldn't use the Limit parameter to the Split() function Jim. Without that, there are no spaces in any of the elements returned.
Apr 27 '09 #2

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1. UPDATE 
  2. GasonNew 
  3. SET 
  4. GoldacrePartNumber = GetSubString(GoldacrePartNumber, ' ', 0), 
  5. GasonDescription = GetSubString(GoldacrePartNumber, ' ', 1);
Expand|Select|Wrap|Line Numbers
  1. Public Function GetSubString(varInput As Variant, _
  2.                         strDelimiter As String, _
  3.                         intSubStringOrdinal As Integer) As Variant
  4.  
  5.     On Error Resume Next
  6.     GetSubString = Split(varInput, strDelimiter)(intSubStringOrdinal)
  7.  
  8. End Function
Apr 27 '09 #3

JustJim
Expert 100+
P: 407
@NeoPa
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?
Apr 27 '09 #4

JustJim
Expert 100+
P: 407
Hi Fish,

However, IMHO, a more elegant (and maybe more effective) solution is to use SQL/VBA combination.
Expand|Select|Wrap|Line Numbers
  1. GetSubString = Split(varInput, strDelimiter)(intSubStringOrdinal)
Isn't this only going to get me the single element referenced by intSubStringOrdinal, If I use the same example as above, the initial field being "S/N1234 Big End Cog" and I send a 1 as intSubStringOrdinal, I'm only going to get "Big". Is that not the case? Then I'd have to keep sending incrementing intSubStringOrdinal 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
Apr 27 '09 #5

NeoPa
Expert Mod 15k+
P: 31,712
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 straightforwardly, within a SQL UPDATE structure as Fish suggests.
Apr 27 '09 #6

FishVal
Expert 2.5K+
P: 2,653
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.
Apr 27 '09 #7

JustJim
Expert 100+
P: 407
Thank you both for your time and expertise. We'll call that one solved shall we?

Jim
Apr 28 '09 #8

NeoPa
Expert Mod 15k+
P: 31,712
What are your thoughts after all that Jim?
Apr 28 '09 #9

JustJim
Expert 100+
P: 407
@NeoPa
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.
Apr 28 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.