Memo Field Delete last line

[bobdydd]

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

 

[Phil]

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

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

 

[John W. Vinson]

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

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

 

[David Hare-Scott]

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

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

 

[Access Developer]

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.

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.

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.

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.