Memo Field Delete last line

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

  1. bobdydd

    bobdydd Guest

    Hi

    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
    CONSETT
    Co Durham
    DH32 9FE
    United Kingdom
    Phone:0000 50000

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

    Thanks for any help you can give
     
    bobdydd, Mar 21, 2013
    #1
    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)
    Else
    OutMemo = !txtBuyerAddressBank
    End If
    Debug.Print OutMemo
    .MoveNext
    Loop
    .Close
    Set TblBlobs = Nothing
    End With

    End Sub

    Phil
     
    Phil, Mar 21, 2013
    #2
    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) &
    "Phone:")


    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Mar 22, 2013
    #3
  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
     
    David Hare-Scott, Mar 22, 2013
    #4
  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 Amazon.com page
    http://www.amazon.com/Rebecca-Riordan/e/B001IODGNK). There are other works
    on the subject but this one is, IMO, well-written, intuitive, and easy to
    follow.
     
    Access Developer, Mar 22, 2013
    #5
    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.
Loading...