How would I capture the 3rd segment of a url in an access 2010 query?

I have a URL in a field and I want to separate the 3rd segment/section which is the product title and then group the records on it.

In my table Field name Q_LOC has somethiing like the following 2 examples:

In a Query design grid I want to add a field to these records as Prod with phaser-6280, or workcentre-5300-series etc.
Mitch SwetskyBusiness AnalystAsked:
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.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Write yourself a generic function to split a string based on a delimiter using Split() (I'd post the one I use, but it's copyrighted).

 Then write a function to take the url, strip off the http://, then call the function above to return the 3rd token of the string based on slash (/) as a delimiter.

From your query, call the function as a column:


Mitch SwetskyBusiness AnalystAuthor Commented:
Thank you Jim,
I  would need help writing those functions and was looking for another way.
I tried Split but it is an unrecognised function in the query design window.

pls try
Function ThirdElem(url As String) As String
ThirdElem = Split(url, "/")(4)
End Function

Open in new window

place it in a module of your access db and then use it your query


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
Mitch SwetskyBusiness AnalystAuthor Commented:
That was Easy, and understandable.
Thank you very much.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:

 While easy, it doesn't give you much flexibility.   When your approaching problems in your apps, you should always find generic solutions.  Here's an EE article that you can use with generic split() functionality:

On top of that, you could now write a procedure like this:

Public Function ReturnNthTokenFromString(strInput as string, strdelimiter as string, intToken as integer) as string

    Dim strArr() as String

    strArr = SplitMultiDelims(strInput , strdelimiter )

    If UBound(strArr)>=intToken-1 then
      ReturnNthTokenFromString = strArr(intToken-1)
      ReturnNthTokenFromString  =""
   End If

End Function

  Now you can use this any time you need to return the nth token from a string based on a delimiter.   next time, it might not always be the 3rd item you want.

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.