parsing string into several pieces in access query -separated by "\"

Posted on 2012-09-05
Last Modified: 2012-09-05
 CODES_1ST: Left([CODES_1],InStr([CODES_1],"\")-1)
returns  21

CODES_2ND: Mid(Left([CODES_1],InStr(InStr(1,[CODES_1],"\")+1,[CODES_1],"\")-1),InStr(1,[CODES_1],"\")+1)
returns 717


What syntax can i use to get the 3rd, 4th and fifth segments of these strings without the "\"s? Each should go into its own field. I am using Access 2007.

Question by:LindaOKSTATE
    LVL 61

    Accepted Solution

    Add a function to a standard module:

    Function GetPartialCode(strData, intPosition) as string
        Dim s() as string
        s = Split(strData,"\")
        GetPartialCode = s(intPosition)
    End Function

    Open in new window

    And call it with columns set up in your query like this:

    FirstSegment: GetPartialCode(YourField, 0)
    SecondSegment: GetPartialCode(YourField, 1)
    ThirdSegment: GetPartialCode(YourField, 2)
    FourthSegment: GetPartialCode(YourField, 3)


    Author Comment

    Is there not a way to just get it through the query?  I really wasn't wanting to deal with adding functions to a module.  But, if that is the only way...
    LVL 61

    Expert Comment

    You probably could through multiple large expressions in your query, but IMO this is the cleanest/simplest approach (I personally would not want to tackle those statements when the VBA code for this is so streamlined and easy to maintain).

    Author Comment

    Ok, Thank you.
    LVL 61

    Expert Comment

    The real benefit of using a function like that from my perspective is that nothing needs to be changed if you need additional or different segments of that data.

    The call from your query will always be similar to this:

    GetPartialCode(YourField, 0)

    ... you just need to change the number that represents the 'position'  (zero-based) of the segment you want to return.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now