Memo Field Delete last line

Discussion in 'Access General' started by bobdydd, Mar 21, 2013.

  1. bobdydd

    bobdydd Guest


    In VBA

    I have a Memo field called txtBuyerAddressBank that contains
    customer addresses and phone numbers.

    I want to delete the last line with the phone number:
    Micky Mouse
    45 Staple Pie
    Co Durham
    DH32 9FE
    United Kingdom
    Phone:0000 50000

    So that I end up with:
    Micky Mouse
    45 Staple Pie
    Co Durham
    DH32 9FE
    United Kingdom

    Thanks for any help you can give
    bobdydd, Mar 21, 2013
    1. Advertisements

  2. bobdydd

    Phil Guest

    This might work (Table name is Blobs... Don't ask why)
    Is specifically looks for the word "Phone:" with the colon, and if it finds
    it, removes everything after it. I am assuming the address has the line feeds
    built in and is exactly as your posting

    Sub RemovePhone()

    Dim MyDb As Database
    Dim TblBlobs As Recordset
    Dim OutMemo As String
    Dim i As Integer

    Set MyDb = CurrentDb
    Set TblBlobs = MyDb.OpenRecordset("Blobs")

    With TblBlobs
    Do Until .EOF
    i = InStr(!txtBuyerAddressBank, "Phone:")
    If i > 0 Then
    OutMemo = Left(!txtBuyerAddressBank, i - 1)
    OutMemo = !txtBuyerAddressBank
    End If
    Debug.Print OutMemo
    Set TblBlobs = Nothing
    End With

    End Sub

    Phil, Mar 21, 2013
    1. Advertisements

  3. Phil's suggestion should work; and alternative would be an UPDATE query

    UPDATE yourtable
    SET memofield = Left([memofield], InStrRev([memofield], Chr(13) & Chr(10))

    This will find the last carriage-return line-feed pair in the field and retain
    only the text up to that position.

    If there might be values without the word Phone you would be safer to use

    UPDATE yourtable
    SET memofield = Left([memofield], InStrRev([memofield], Chr(13) & Chr(10) &


    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    and see also
    John W. Vinson, Mar 22, 2013
  4. A couple of general hints in addition to the specific solutions that you
    have been given.

    Don't store data conglomerated into memos (or text) unless you are sure that
    it is free text, that is it has no internal structure or meaning that you
    may want to extract or edit at some time in the future. If your database is
    more normalised, the address details are in separate fields in this case,
    you won't set traps for yourself for the future. In the case where you are
    converting old data that is not in separate fields it is MUCH better to
    parse it once when the history is loaded into the new system than to have to
    do it on the run at various times during the life of the system.

    If performing bulk updates on your data either by code or action queries,
    unless it is already thoroughly tested, you may save yourself some heartburn
    some day by always doing a quick backup of the database (or table)
    immediately before the update run. In other words don't test on your only
    copy of the data. This may seem obvious but I have seen people who ought to
    know better fall for this.

    David Hare-Scott, Mar 22, 2013
  5. bobdydd's question clearly indicates that the address in the memo field is
    intended to be structured, and he's already run into the need to do
    something to just part of that structure (otherwise, it would be fair to
    say, "there's a high likelihood that, sooner or later, you _will_ need to
    deconstruct the text in order to work on just part of it.

    Take David's advice. I assure you that you will benefit from learning about
    relational database design principles, and, especially, "normalization". A
    book I have often recommended is Rebecca Riordan's _Designing Effective
    Database Systems_ (described on an page There are other works
    on the subject but this one is, IMO, well-written, intuitive, and easy to
    Access Developer, Mar 22, 2013
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
There are no similar threads yet.