Remove lines from Memo field

[bvallez]

I have a memo field that contains lines of text. Each line ends with a carriage return/line feed.



I want to "remove" (or replace with blank space) lines that start with specific text.

Data in memo field:
UPS 1Z1234
UPS 1Z23456
Shipping Charges $25.50
UPS 1Z4567
UPS 1Z8910
Shipping Charges $23.80

I would like to end up with:
UPS 1Z1234
UPS 1Z23456
UPS 1Z4567
UPS 1Z8910

[ajetrumpet]

try searching for "vbcr" or "vbcrlf" in the find/replace dialog when in datasheet view of the table. If I remember right, I did that once and it works.

[bvallez]

I should have been more clear. In my example, I want to remove lines that start with "Shipping Charges"...but retain all others.

See my examples of what data is in the field, and where I want to end up.

[orange]

Just out of curiosity, why not create a table and store individual records that can be linked to your main table (whatever that is) in a 1 to many relationship.

Structuring a memo field, as you are finding out, is not a long term solution.

Just my $.02

[rpeare]

Dim db As Database
Dim rst As Recordset
Dim sSQL As String
Dim sMemo As String
Dim sParsedData As Variant
Dim i As Integer
Dim sMemoConv As String


Set db = CurrentDb

sSQL = "SELECT * FROM tbl_Memos"
Set rst = db.OpenRecordset(sSQL)

rst.MoveFirst

Do While rst.EOF <> True
    sMemo = rst.Fields("memofield")
    sParsedData = Split(sMemo, vbCrLf)
    sMemoConv = ""
    For i = 0 To UBound(sParsedData)
        sline = sParsedData(i)
        If Left(sline, 4) <> "Ship" Then
            sMemoConv = sMemoConv & sline & vbCrLf
        End If
    Next i
    rst.Edit
    rst.Fields("MemoField").Value = sMemoConv
    rst.Update
    Debug.Print sMemoConv
    rst.MoveNext
Loop
rst.Close
Set db = Nothing

this will parse your memo fields and remove any line that starts with "SHIP", you'll have to insert your own table and field names though. Just one word of warning. I am running an update on the same field I'm processing which is a dangerous practice but it's just an example. You will likely want to perform the update on a separate field to make sure yours works before implementing it.

[bvallez]

rpeare, thank you for this code! Accomplished exactly what I needed!

Thanks.