Extracting the part of a letter after "dear whoever" in a memo field

[Mike375]

Extracting the part of a letter after "dear whoever" in a memo field.

I need to do this so as to remove duplicates and "yes" duplicates can be removed from a memo field.

Another alternative would be to remove the date which is between the letterhead and the "dear whoever". The letterhead loses its formatting and each letter looks like the following, although the suburb can be either one or two words.


xyz
Authorised Representative of xyz Pty Ltd
ABN 123 AFSL No 123
Income Replacement
Trauma and TPD
Life Insurance

Wednesday, 4 November 2015

Mr abc xyz
10 abc Road
Castle Cove NSW 2069

Dear xyz,

-------------------------------------------------------------------

The main issue is the date because some have been done on two different days and thus will not come out as duplicates.

The letters are produced by Access filling bookmarks and then the code basically doing a "select all" and "copy" and the pasted to a new record in the table with the memo field.

 

[CJ_London]

you need to identify the text after something uniquely relevent e.g. the word dear

mytext=mid(mystring,instr(mystring,"Dear"))

then find the text after the first instance of a comma,

mytext=mid(mid(mystring,instr(mystring,"Dear")),instr(mid(mystring,instr(mystring,"Dear")),",")+1)

you can't go straight for the comma since it exists in the date line

so someti

 

[Mike375]

Yes, I have been fiddling in that general direction but not yet getting there

Thanks for posting.

 

[arnelgp]

VBA alternative

public sub fnFixMemo()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Long
    Dim lngPos As Long
    Dim strMemo As String
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblMemoField2", dbOpenDynaset)
    
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        Do While Not .EOF
            strMemo = ![Memo] & ""
            
            'Find the WeekdayName in the memo
            For i = 1 To 7
                lngPos = InStr(strMemo, WeekdayName(i))
                If lngPos > 0 Then Exit For
            Next i
            
            'if we found the Weekdayname, find the end of line
            If lngPos > 0 Then
                i = lngPos
                While Mid(strMemo, i, 1) <> VBA.vbCr And i < Len(strMemo)
                    i = i + 1
                Wend
                
                If i > lngPos Then
                    ' join the two string
                    strMemo = Left(strMemo, lngPos - 1) & Mid(strMemo, i)
                End If
            End If
            
            'now find then "Dear mr/ms.xxx,
            lngPos = InStr(strMemo, "Dear")
            If lngPos > 0 Then
                'same thing look for vba.vbaCr in the string
                i = lngPos
                While Mid(strMemo, i, 1) <> VBA.vbCr And i < Len(strMemo)
                    i = i + 1
                Wend
                
                If i > lngPos Then
                    strMemo = Left(strMemo, lngPos - 1) & Mid(strMemo, i)
                End If
            End If
            .Edit
            ![Memo] = strMemo
            .Update
            .MoveNext
        Loop
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Sub

replace the tablename, memo fieldname with your table/field name.

 

[Mike375]

you need to identify the text after something uniquely relevent e.g. the word dear

mytext=mid(mystring,instr(mystring,"Dear"))

then find the text after the first instance of a comma,

mytext=mid(mid(mystring,instr(mystring,"Dear")),instr(mid(mystring,instr(mystring,"Dear")),",")+1)

you can't go straight for the comma since it exists in the date line

so someti

Many thanks, that did the trick.

In fact this one...mytext=mid(mystring,instr(mystring,"Dear")) was enough.