• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 286
  • Last Modified:

Query to Parse 1 field to 7

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,

1 Solution
Patrick MatthewsCommented:
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
' http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21221177.html

' 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


BassoonAuthor Commented:
Awesome!!! Thank you so much! Worked perfectly the first time.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now