Access 2003 - deleting numbers in a variable length thread.
Question posted by: HSXWillH
(Newbie)
on
July 19th, 2008 11:57 PM
This is kind of an odd question, I'm hoping the answer is easy for Access 2003.
I do design for horse racing data dumps and in trying to group certain types of race information, I find many minute differences in data that ultimately don't matter. I'm trying to parse those differences out, but each string is different lengths and the useful data is not in the same set position.
The field in question is LongClass.
Some of the initial dumps I'm inputting and then modifying into my own usage involve the following data:
Alw 27000N1X
Alw 10000s
Alw 4000s
Alw 7500N2L
Alw 33000N$MY
Now, what I'm wanting to do is for each of these records, the numbers in the middle are not useful to me. I want a method to parse those numbers out and be left with
Alw N1X
Alw s
Alw s
Alw N2L
Alw N$MY
Is there any easy way to do this given the uncertainty of the length in the data I want to erase and the numbers that could come as part of the valid descriptor (the N1L) portion?
|
|
July 20th, 2008 12:30 AM
# 2
|
Re: Access 2003 - deleting numbers in a variable length thread.
I'm sure someone will come up with a better answer, but this will work as long as the format of the String is consistent:
- Public Function fParseString(strMyString As String)
-
Dim varString As Variant
-
Dim strPart1 As String
-
Dim strPart2 As String
-
Dim intCounter As Integer
-
-
varString = Split(strMyString)
-
-
strPart1 = varString(0)
-
-
'find 1st non-numeric value in the 2nd element, then extract
-
'from that point on
-
For intCounter = 1 To Len(varString(1))
-
If Not IsNumeric(Mid$(varString(1), intCounter, 1)) Then
-
fParseString = strPart1 & " " & Mid$(varString(1), intCounter)
-
Exit Function
-
End If
-
Next
-
End Function
- Debug.Print fParseString("Alw 27000NIX")
-
Alw NIX
- Debug.Print fParseString("Alw 10000s")
-
Alw s
- Debug.Print fParseString("Alw 4000s")
-
Alw s
- Debug.Print fParseString("Alw 7500N2L")
-
Alw N2L
- Debug.Print fParseString("Alw 33000N$MY")
-
Alw N$MY
|
|
July 20th, 2008 01:05 AM
# 3
|
Re: Access 2003 - deleting numbers in a variable length thread.
I'm rather code-stupid...so please bear with me. Where do I put this information in the database?
I assume the top portion goes in a module, but what about the bottom portion?
|
|
|
July 20th, 2008 01:46 AM
# 4
|
Re: Access 2003 - deleting numbers in a variable length thread.
Quote:
I'm rather code-stupid...so please bear with me. Where do I put this information in the database?
I assume the top portion goes in a module, but what about the bottom portion?
|
The bottom portion is just for demo purposes, and provides the results for several Strings sent to the Function. The actual code itself can go into a Standard Code Module. Are you going to be working with a single String at a time, or with Strings contained in a Field within a Table?
|
|
|
July 20th, 2008 01:52 AM
# 5
|
Re: Access 2003 - deleting numbers in a variable length thread.
Okay, I put this module in the database, and then in my query, I put the fParseString("Text"). This works for the query in that specific Text. However, I'm inputting thousands of records at a time so I tried to change "Text" to [LongClass] as that's the subject data field. I get a Run-Time Error 9, SubScript out of range at
For intCounter = 1 To Len(varString(1))
in the module.
I'm admittedly terrible with coding so I'm trying to figure out how to get the LongClass field represented in each record in the query. Does this make sense or am I confusing the matter?
|
|
|
July 20th, 2008 01:54 AM
# 6
|
Re: Access 2003 - deleting numbers in a variable length thread.
Quote:
The bottom portion is just for demo purposes, and provides the results for several Strings sent to the Function. The actual code itself can go into a Standard Code Module. Are you going to be working with a single String at a time, or with Strings contained in a Field within a Table?
|
The data will come from the pre-established [LongClass] field already in the database.
Thank you for the assistance, I'm just trying to start out with coding.
|
|
|
July 20th, 2008 01:20 PM
# 7
|
Re: Access 2003 - deleting numbers in a variable length thread.
Quote:
The data will come from the pre-established [LongClass] field already in the database.
Thank you for the assistance, I'm just trying to start out with coding.
|
You will be creating a Calculated Field in a Query, then passing the value in the [LongClass] Field to the Function, something like this:
- SomeFieldName:fParseString([LongClass])
If you are still stuck, let me know and I'll send you a simple Demo as an Attachment to illustrate how this should be done.
|
|
|
July 21st, 2008 02:12 PM
# 8
|
Re: Access 2003 - deleting numbers in a variable length thread.
Quote:
You will be creating a Calculated Field in a Query, then passing the value in the [LongClass] Field to the Function, something like this:
- SomeFieldName:fParseString([LongClass])
If you are still stuck, let me know and I'll send you a simple Demo as an Attachment to illustrate how this should be done.
|
Okay, this worked. Thank you for all your help.
|
|
|
July 21st, 2008 05:28 PM
# 9
|
Re: Access 2003 - deleting numbers in a variable length thread.
Quote:
Okay, this worked. Thank you for all your help.
|
You are quite welcome.
Not the answer you were looking for? Post your question . . .
189,323 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).
|
