[Webinar] Learn how to a build a cloud-first strategyRegister Now

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

parsing a memo field

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.
  • 2
1 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
dastaubAuthor Commented:
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
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.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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