String manipulation woes

  • Thread starter Thread starter bobdydd
  • Start date Start date
B

bobdydd

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
 
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".
Click to expand...

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
 
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
Click to expand...

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
 
On 26 July, 01:50, John W. Vinson <jvinson@STOP_SPAM.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
 
Back
Top