LOCATING TEXT IN A STRING

[Guest]

Need some help. I have a database (ExtractData) that has a field (claim
number) and I need to get to specific characters in the field. The data in
there can be any of the following:
123456789
123-456789
123CN456789
123-456789-Name
123-CN456789000

It is easy to pull the first 3 characters which I need for one field, but I
need to get to the next 6 numeric characters in a query and extract them.
Stumped on how I would deal with all the different formats.

Your help is always appreciated

 

[Guest]

Here is a function that will do what you want. If you want to use it in a
query, standard module and include it in the query.

Call it form the query like this in the Fields row:

MidSix: GetSix([OriginalFieldName])

Here is the function:

Public Function GetSix(strAll As String) As String
Dim intX As Integer
Dim strFix As String

strFix = Right(strAll, Len(strAll) - 3)
For intX = 1 To Len(strFix)
If IsNumeric(Mid(strFix, intX, 1)) Then
GetSix = GetSix & Mid(strFix, intX, 1)
If Len(GetSix) = 6 Then
Exit For
End If
End If
Next intX
End Function

 

[Guest]

Fantastic! Works like a charm. Thanks Klatuu!!!

Here is a function that will do what you want. If you want to use it in a
query, standard module and include it in the query.

Call it form the query like this in the Fields row:

MidSix: GetSix([OriginalFieldName])

Here is the function:

Public Function GetSix(strAll As String) As String
Dim intX As Integer
Dim strFix As String

strFix = Right(strAll, Len(strAll) - 3)
For intX = 1 To Len(strFix)
If IsNumeric(Mid(strFix, intX, 1)) Then
GetSix = GetSix & Mid(strFix, intX, 1)
If Len(GetSix) = 6 Then
Exit For
End If
End If
Next intX
End Function

Need some help. I have a database (ExtractData) that has a field (claim
number) and I need to get to specific characters in the field. The data in
there can be any of the following:
123456789
123-456789
123CN456789
123-456789-Name
123-CN456789000

It is easy to pull the first 3 characters which I need for one field, but I
need to get to the next 6 numeric characters in a query and extract them.
Stumped on how I would deal with all the different formats.

Your help is always appreciated