473,320 Members | 1,707 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Split Function and resultant 1D array

JustJim
407 Expert 256MB
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
9 3395
NeoPa
32,556 Expert Mod 16PB
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
2,653 Expert 2GB
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
407 Expert 256MB
@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
407 Expert 256MB
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
32,556 Expert Mod 16PB
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
2,653 Expert 2GB
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
407 Expert 256MB
Thank you both for your time and expertise. We'll call that one solved shall we?

Jim
Apr 28 '09 #8
NeoPa
32,556 Expert Mod 16PB
What are your thoughts after all that Jim?
Apr 28 '09 #9
JustJim
407 Expert 256MB
@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

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

Similar topics

4
by: Brian Glen Palicia | last post by:
My goal is to accept input from the user into a text box and then parse the data using split(). The first step is this tiny program to test the split() function. It runs in IE, but in Mozilla it...
7
by: lkrubner | last post by:
The PHP scripting language has the array_unique() function that gets the unique, non-redundant values out of an array. Does Javascript have anything similar?
20
by: Tom van Stiphout | last post by:
I'm about to write a function like below, which I'm going to call a lot of times. So I care about possible memory leaks. I think whether I should use Erase or not depends on whether Split creates...
19
by: David Logan | last post by:
We need an additional function in the String class. We need the ability to suppress empty fields, so that we can more effectively parse. Right now, multiple whitespace characters create multiple...
3
by: Ben | last post by:
Hi I am creating a dynamic function to return a two dimensional array from a delimeted string. The delimited string is like: field1...field2...field3... field1...field2...field3......
1
by: dotnettester | last post by:
Hi, I need to split a string and then take the 0th element of the resultant array. what could be a good shortcut? like 1 statement instead of 3,4 Thnx
10
by: pantagruel | last post by:
Hi, I'm looking for an optimal javascript function to split a camelcase string and return an array. I suppose one could loop through the string, check if character is uppercase and start...
3
by: edoardo.poeta | last post by:
I'm a dummy. I have a basic knowledge of javascript and I want to split a string, but I receive an error at line 15. Where my error in make the array? Why? Can someone help me to resolve? Thank's....
14
by: Stevo | last post by:
If you split a string into an array using the split method, it's not working the way I'd expect it to. That doesn't mean it's wrong of course, but would anyone else agree it's working somewhat...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.