• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • 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,

-/-/698500420/INTRCO/-/-/100P
TEM/05000/300100/160020/34097/-/2170
TEM/11600/-/-/02480/-/-
0
Bassoon
Asked:
Bassoon
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)
    Else
        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)
    Else
        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


Regards,

Patrick
0
 
BassoonAuthor Commented:
Awesome!!! Thank you so much! Worked perfectly the first time.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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