Parsing a string of data

Posted on 2011-04-20
Last Modified: 2012-05-11
I need a function to parse data between (- ) or ( , ) or ( . ) or ( / ).  These could be in different locations within the string.  I need to be able to parse between the first (.) and the second (.) or any one of the other (- ) or ( , ) or ( . ) or ( / ).  

String is as follows:  .AACTS 2001.6400.680.SDC

I need to separate the data into a table as
Field1 = ACCTS 2001
Field2 = 680
Field3 = 6400
Field4 = SDC

Thank You
Question by:frank_guess
    LVL 22

    Accepted Solution

    Add this function in a module:
    Function GetStringItem(strData As String, intItem As Integer) As String
        Dim strParts() As String
        strData = Replace(strData, "-", ".")
        strData = Replace(strData, "/", ".")
        strData = Replace(strData, ",", ".")
        strParts = Split(strData, ".")
        If UBound(strParts) >= intItem Then
            GetStringItem = strParts(intItem)
        End If
    End Function

    Open in new window

    For example
    ?GetStringItem(".AACTS 2001.6400.680.SDC",1)
    AACTS 2001
    ?GetStringItem(".AACTS 2001.6400.680.SDC",2)

    You can use this function in a query to split the field. Make this query an update or append query to save the data in a table. Since I don't know where the data is coming from (do you have a table with all the data in it?) I can't tell you exactly what to do.
    LVL 24

    Expert Comment

    Use Split

    Option Compare Database
    Option Explicit
    Public Sub splitIt()
        Dim aString As String: aString = "AACTS 2001.6400.680.SDC"
        Dim coll As Variant
        coll = Split(aString, ".")
    End Sub

    Open in new window

    LVL 47

    Assisted Solution

    by:Dale Fye (Access MVP)
    Use the split function in combination with the replace function, something like:

    Dim myArray() as variant

    myArray = Split(Trim(Replace(Replace(Replace(Replace([fieldname], "-", " "), ".", " "), "/", " "), ",", " ")))

    This will create an array of values that can be referred to as myArray(0)-myArray(n)

    I generally use the Lbound(myArray) and UBound(myArray) to identify the upper and lower end of the array.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Oops, forgot the delimiter (" ") in the split part of that operation.

    Author Comment

    Let me take a look at both and I will get back to you, once I have tried each method.  Thanks

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Article by: Leon
    Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
    If you use NetMotion Mobility on your PC and plan to upgrade to Windows 10, it may not work unless you take these steps.
    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.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    755 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

    20 Experts available now in Live!

    Get 1:1 Help Now