How to use Replace() function in Access query to remove characters from beginning and end of Part numbers.

I have a big list of parts with prefixes and suffixes to be stripped so they can be compared to another list. How would I use the Replace() function in a query to do that? Ex:

HX01 AH154704 E                     returns   AH154704
AX01 19M7805 --                           returns   19M7805
JL01 DE20999 C_242828        returns   DE20999

Removes everything before first space and after last space. Would a nested Replace function work?  How would that look?
Jay WilliamsOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Replace() will not work for your situation.  It requires that you substitute one string for another and that isn't what you are doing.  You will need to use a combination of functions to zero in on the "middle" part of the string.

OutString = Left(Mid(InString, InStr(InString, " ") + 1), InStrRev(Mid(InString, InStr(InString, " ") + 1, " ") -1)

This is untested so you may have to make adjustments.  Essentially it is using the Mid() and Left() functions to extract the data.    Mid() takes three arguments, the string, the starting place, and the length which is optional.  The starting place is 1 position past the first space -- InStr(InString, " ") +1.  The length isn't specified so in the first case will select "AH154704 E".  That string is then processed by the Left() function where we use the InStrRev() to find the first " " from the right edge -1 to find the end of the "middle" part.

I'm sure that there are several variations of this so please see what works for your data.  The given example will fail if the string doesn't have three parts.

If the string is more complex, you might want to consider writing a function to do the extraction and then just calling the function.

Let this be a lesson to those who insist on violating first normal form and mushing multiple attributes into a single field.  Extracting the parts is a lot harder than combining them initially and if you are dealing with optional "parts" the separation might even prove to be impossible.
Dale FyeOwner, Developing Solutions LLCCommented:
since there is no consistency to the prefix or suffix that is being deleted, I would advise creating a query to generate those set of characters between the first and second space characters.

I use a function called fnParseText( ) which allows me to pass a variant, a delimiter, and a indicator of the position.

Public Function fnParseText(ParseWhat as Variant, Position as integer, _
                 Optional Delimiter as string = ",") as Variant

    'Returns the segment of as string identified by the Position argument after
    'splitting the ParseWhat argument at every occurance of the Delimiter string
    'Example:  fnParseText("Now is the", 2, " ")  would return "is"

    Dim myArray() as string

    fnParseText = NULL
    if IsNull(ParseWhat) then Exit Function
    if Position < 1 then Exit Function 

    'parse the string based on the Delimiter argument
    myArray = Split(ParseWhat, Delimiter)

    'If the position you are looking for is greater than the number of segments in 
    'the string, then exit the function
    if Position > ubound(myArray() + 1 then Exit Function

    fnParseText = myArray(Position - 1)

End Function

Open in new window

With this function, you could use something like:

NewField: fnParseText([FieldName], 2, " ")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jay WilliamsOwnerAuthor Commented:
Thanks guys.  There aren't always 3 parts, so I guess the function is the way to go.  Not really sure how to use it though. Do I lay the code in a module and the NewField calls the function when the query runs?
Jay WilliamsOwnerAuthor Commented:
Got it. Worked like a charm AND learned something great in the meantime. Makes sense. Thanks a bunch!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.