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?
8  1616 
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
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?
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?
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?
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.
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.
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.
Okay, this worked. Thank you for all your help.
You are quite welcome.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Peter Frost |
last post by:
Please help
I don't know if this is possible but what I would really like to do is
to use On Error Goto to capture the code that is being executed when
an error occurs.
Any help would be much...
|
by: Cheryl Langdon |
last post by:
Does anyone know if there is a way to globally turn off ALL control
tips in Access 2003 using VBA code?
Thanks.
--- CL
|
by: Gary Bond |
last post by:
Hi All,
I am a bit stuck with a project: Specifically, when making a database like
engine in 'the old days', I would have wrapped a record class with a stream
class, so I could have a file of...
|
by: dekker.erik1 |
last post by:
hello,
I would like to know if there is a way in ms access 2003 to add
consequtive numbers to a new column to 'count' the records. This count
should start over by every by variable. for example:...
|
by: Crazineko |
last post by:
Hello all,
I am fairly new to Access and am running into a problem. I am looking at a table that has all phone numbers that have called our business for the month of November. However, many of...
|
by: pranav13 |
last post by:
hi all,
im pranav. i have a table with checkbox, sno, book name, book price as coloumns in it. Everything is 5ne while adding and deleting rows.
But my requirement is that, now i want to update...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
