parsing a memo field

Posted on 2006-06-07
Last Modified: 2013-12-25
Example 1:

Example 2:
Note:Rec'd Full & Final payment fm Atty...all remaining charges adjusted off per Settlement agreement

The above two  examples are two of 1,000's of memo fields that are inside a billing software database that I am interested in importing into an Access Table for better report writing ability.  I use ODBC to view the table in Access as it is currently in a FoxPro table.  I want to parse it so that Type, Cnum, Srce, Note, and Totl are there own fields.  The format of memo fields is four characters to the left of the ":" are the contents description, then the contents.  If the description is a Note, the contents can take up multiple rows inside the memo field.
Question by:dastaub
    LVL 9

    Accepted Solution

    Dim strTemp() as string, strContent() as string, strFieldValue as String
    Dim strNote() as String
    Dim i as Integer
    if InStr(strMemo,"Note:") > 0 then
        strNote = Split(strMemo,"Note:")
        strFieldValue = strNote(1)                'now fieldvalue consists the note
        strTemp = Split(strNote(0),vbcrlf)  
        For i = 0 to ubound(strTemp)
            strContent = Split(strTemp(i),":")
            strFieldValue = strContent(1)        'strFieldValue now consists the required        
        Next i
        strTemp = Split(strMemo,vbcrlf)        'Assuming that the memo value is copied to    
                                                              'strMemo variable
        For i = 0 to ubound(strTemp)
            strContent = Split(strTemp(i),":")
            strFieldValue = strContent(1)        'strFieldValue now consists the required value
        Next i
    End if

    Author Comment

    I used your logic and got the solution.  I had to parse the memo field twice, once by carriage return and then by the ":".
    It worked successfully.  the Srce, Totl, Type, Cnum are constants that appear in the memo fields in front of the data and separated by a carriage return.
    Do While Not RS2.EOF  
        strTemp = Split(RS2!notes, vbCrLf)
        For i = 0 To UBound(strTemp)
        strTemp2 = Split(strTemp(i), ":")
           For j = 0 To UBound(strTemp2)
            If strTemp2(0) = "Srce" Then RS3!Srce = Trim(strTemp2(1)) & " "
            If strTemp2(0) = "Totl" Then RS3!Totl = Trim(strTemp2(1)) & " "
            If strTemp2(0) = "Type" Then RS3!Type = Trim(strTemp2(1)) & " "
            If strTemp2(0) = "Cnum" Then RS3!Cnum = Trim(strTemp2(1)) & " "
          Next j
         Next i
        RS3!trans_num = RS2!trans_num
    LVL 9

    Expert Comment

    Really very nice job u did and this the complete feed back. If all the authors post this like feed back definitely it will be the documentation of solutions for problems.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    759 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

    14 Experts available now in Live!

    Get 1:1 Help Now