Access 2003 - deleting numbers in a variable length thread.

[HSXWillH]

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?

[ADezii]

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

[HSXWillH]

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?

[ADezii]

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?

[HSXWillH]

Okay, I put this module in the database, and then in my query, I put the fParseString("T ext"). 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?

[HSXWillH]

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.

[ADezii]

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.

[HSXWillH]

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.

[ADezii]

Okay, this worked. Thank you for all your help.

You are quite welcome.