Hi Everyone

I have 2 memo fields
1. txtBuyerAddressEbay
2. txtBuyerAddress

In txtBuyerAddressEbay the address appears thus:
Bert Smith
1 Buddy Holly Drive
Harlow
Essex CM1 1BT
United Kingdom

On the double click event I am using:
Me!txtBuyerFirstLastName = Me!txtBuyerFirstLastName

But what I would like to do is remove the customer name so that it
appears
with just the address. Like this:

1 Buddy Holly Drive
Harlow
Essex CM1 1BT
United Kingdom

Grateful for anyhelp

Are you saying that you have a field (memo, named txtBuyerAddressEbay) that
contains more than one 'kind' of fact. If so, your data structure violates
the database design principal of "one field, one fact".

By storing a name and a street address and a city and ... all in the same
field, you are making you (and Access) work a lot harder than you need to.

One fact, one field ... one field for FirstName, one for LastName, one for
StreetAddress, one for ...

Now your table consists of records that you can (easily) sort/select by City
or by LastName or by ...

And by the way, if we're to believe the names of those imply what's stored
in them, you have a second "memo" field that ALSO stores address
information, but for a different group of people. This is ALSO not a good
idea when using a relational database. OK for spreadsheets, not a good idea
for databases. Why, you ask? Well, what happens when you have a person who
fits in more than one group ... redundant data. What happens when you add a
new group? New field and/or new table and/or new queries and/or new forms
and/or new reports and/or ... a real maintenance nightmare!

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP


"bobdydd" <reallyuseful2004 (AT) yahoo (DOT) co.uk> wrote

bobdydd wrote:

Sub MemoAR()
Dim s As String
Dim ar As Variant
Dim intFor As Integer
s = "Bert Smith" & vbNewLine & _
"1 Buddy Holly Drive" & vbNewLine & _
"Harlow" & vbNewLine & _
"Essex CM1 1BT" & vbNewLine & _
"United Kingdom"

ar = Split(s, vbNewLine)

MsgBox "First element index is " & LBound(ar) & vbNewLine & _
"last element index is " & UBound(ar)

'start at second element
s = ""
For intFor = (LBound(ar) + 1) To UBound(ar)
s = s & ar(intFor) & vbNewLine
Next
MsgBox s

End Sub

On 24 July, 23:42, "Jeff Boyce" <nonse... (AT) nonsense (DOT) com> wrote:
Thanks for the advice..........but you are on the wrong trail. The
field
txtBuyerAddressEbay is filled with raw data that has arrived from
eBay
and I have no control over that until it gets into my database.
It contains the following:
Bert Smith
1 Buddy Holly Drive
Harlow
Essex CM1 1BT
United Kingdom

Only once it is in my database can I extract the elements you suggest
But for the moment I would just like to get rid of the 1st Line "Bert
Smith"

Regards
and thanks for the attention

If you can guarantee that the first 2 words are first name (or initial) and
surname then there is no problem. Use the Instr function to look for the
spaces.
If the peson's name is only a surname then it's not so easy. You could check
on the first character of the second word to see if it is numeric which
would resolve
Smith 1 Holly Drive, but would not resolve Smith Holly house

Phil
"Salad" <oil (AT) vinegar (DOT) com> wrote

On Fri, 24 Jul 2009 15:15:47 -0700 (PDT), bobdydd
<reallyuseful2004 (AT) yahoo (DOT) co.uk> wrote:

If... and it's a HUGE if, given the vagaries of how addresses might be
represented... you can be *certain* that you want to discard only the first
line, up to the first cr/lf, you can use

Mid([txtBuyerAddressEbay], InStr([txtBuyerAddressEbay], Chr(13) & Chr(10)) + 2

--

John W. Vinson [MVP]

On 26 July, 01:50, John W. Vinson <jvinson (AT) STOP_SPAM (DOT) WysardOfInfo.com>
wrote:

Mid([txtBuyerAddressEbay], InStr([txtBuyerAddressEbay], Chr(13) & Chr
(10)) + 2

Thanks for everyones help. The code above did exactly what I was
looking for. It
may clunk out on certain addresses, but since I am the end user of the
database
I will forgive myself.

Anyway.Thank to all that helped.

Regards