Query to Parse 1 field to 7

Posted on 2010-01-08
Last Modified: 2013-11-29
I have a table in Access 2003 that needs to be parsed from 1 column to 7. The character that would split the text is "/" (see examples below). How would I do this with criteria in a query? I do not know VBA very well but if someone can get me started, can probably use that to. Thanks,

Question by:Bassoon
    LVL 92

    Accepted Solution

    Hello Bassoon,

    Add this function to a regular VBA module:

    Public Function RetrieveSplitItem(Text As String, Separator As String, Item As Variant, _
        Optional Limit As Long = -1, Optional CaseSen As Boolean = False)

    ' Function based on post by Brad Yundt

    ' Returns a specified substring from a larger string (Text) separated by a specified
    ' character sequence (Separator).  Limit constrains the maximum number of array
    ' elements found in the Text.  E.g., if the delimiter would normally specify 10
    ' elements but Limit = 6, then the sixth element contains what normally would have
    ' been elements six through ten (with delimiter included!).  This follows the VBA
    ' Split functionality, so -1 indicates no limit

        Dim X As Variant

        If CaseSen Then
            X = Split(Text, Separator, Limit, vbBinaryCompare)
            X = Split(Text, Separator, Limit, vbTextCompare)
        End If
        If IsNumeric(Item) And (Item < 1 Or Item > (UBound(X) + 1)) Then
            RetrieveSplitItem = CVErr(xlErrNA)
        ElseIf Not IsNumeric(Item) And Item <> "L" And Item <> "l" Then
            RetrieveSplitItem = CVErr(xlErrNA)
            If Item = "L" Or Item = "l" Then Item = UBound(X) + 1
            RetrieveSplitItem = X(Item - 1)
        End If
    End Function

    Use it in a query like this:

    SELECT RetrieveSplitItem(YourColumn, "/", 1) AS Item1, RetrieveSplitItem(YourColumn, "/", 2) AS Item2, ...
          RetrieveSplitItem(YourColumn, "/", 7) AS Item7



    Author Closing Comment

    Awesome!!! Thank you so much! Worked perfectly the first time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 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

    18 Experts available now in Live!

    Get 1:1 Help Now