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: - 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 GoldacrePartNumber, GasonDescription from GasonNew"
-
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
-
-
Do Until rs.EOF
-
strInput = rs!GoldacrePartnumber & " "
-
varInput = Split(strInput, " ", 2)
-
rs.Edit
-
rs!GoldacrePartnumber = varInput(0)
-
rs!GasonDescription = RTrim(varInput(1))
-
rs.Update
-
-
rs.MoveNext
-
Loop
-
-
CleanUpAndLeave:
-
rs.Close
-
Set rs = Nothing
-
Set db = Nothing
-
DoCmd.Hourglass False
-
DoCmd.SetWarnings True
-
Exit Sub
-
-
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
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.
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
@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?
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 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
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.
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.
Thank you both for your time and expertise. We'll call that one solved shall we?
Jim
NeoPa 32,556
Expert Mod 16PB
What are your thoughts after all that Jim?
@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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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?
|
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...
|
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...
|
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......
|
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
|
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...
|
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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
|
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...
| |