How can I Extract part of a string in a query?

[Ryan Gunnin]

I have a field in my Query that contains a 10 digit part
number (ex...6035033319). I would like to return just the
ten digit part number and none of the other characters in
the field a sample of the filed would be: (FRO 0307
COMPLETE BY 9/26. PN 6035023319). The 10 digit part
number might not be in the same position in each record.
I can make the query return just those records with the
Part number in it by using ( like"*##########*) as my
criteria. I know you've got to be able to do it...I just
don't know how.

 

[Fredg]

Ryan,

If the position of the 10 characters within the string varies,
you will have to check each character in the string
for it's Asc value.

Public Function GetNumbers(StringIn as String) as String
' will find a group of 10 consecutive number
' anywhere within a string.

Dim intX as Integer
Dim intY as Integer
Dim intCounter as Integer
Dim PartString as String

For intY = 1 to Len([StringIn])
intX = Asc(Mid([StringIn],intY))

'If it is between 48 and 57 ( a number) increment a counter by 1.
'Continue to the next character.
'If it's a number, increment again by 1.
'If it's not a number, reset the counter to 0.

If intX >= 48 and intX <= 57 Then
PartString = PartString & Mid([StringIn],intY,1)
intCounter = intCounter +1
Else
PartString = ""
intCounter = 0
End If
If intCounter = 10 Then
GetNumbers = PartString
Exit functon
End If
Next intY

When intCounter reaches 10, bingo! You've got the number.

You will need a User Defined function for this, placed in a Module.
Add whatever error handling is necessary.

You can then call the function from the query.
Exp: GetNumbers([FieldName])
Add criteria to the [FieldName] field:
Is Not Null

 

[Cheryl Fischer]

How about ...

Mid([PartNum], InStr([PartNum], "PN ")+3, 10)

 

[RobFMS]

Ryan

You can use the Split() function (Access 2000 and later) to extract the
"words" from the strings. Use the space character as a delimiter.

I am not sure how to resolve the situation of the 10-digit string not
appearing in a specific position. Since you know the data the best, you
would have to see what patterns may (or may not) be occuring to prove you
clues on how to resolve this.

HTH

--

Rob

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Need to launch your application in the appropriate version of Access?
http://www.fmsinc.com/Products/startup/index.asp

Need software tools for Access, VB, SQL or .NET?
http://www.fmsinc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-