Merge multiple address fields

[jez123456]

Hi experts

My users store contact addresses in 4 fields ie

Address1
Address2
Address3
Address4

The data is a bit inconsistent in that some records may have data in
Address1 and Address3 missing out the Address2 field.

My aim is to record the data in a multi-line text field.

Is there a program I can run that will join the fields together (removing
the blank data) and input this data into a new multi-line Address field?

 

[Armen Stein]

Hi experts

My users store contact addresses in 4 fields ie

Address1
Address2
Address3
Address4

The data is a bit inconsistent in that some records may have data in
Address1 and Address3 missing out the Address2 field.

My aim is to record the data in a multi-line text field.

Is there a program I can run that will join the fields together (removing
the blank data) and input this data into a new multi-line Address field?

First of all, if any of those fields contain City, State or Postal
Code, I don't recommend merging them all together. A much better set
of address fields is something like:

Address1
Address2
City
StateProv
Country (if any addresses will be outside North America)
PostalCode

That said, you can merge your fields together by concatenating your
fields in an update query.

Create a new field in your table called say FullAddress.

Update FullAddress to something like (air code):

Address1
& ((chr13)+ chr(10) + Address2)
& ((chr13)+ chr(10) + Address3)
& ((chr13)+ chr(10) + Address4)

I wrote these on different lines for clarity - this would all be on
one line in the query designer. Use the Zoom feature to see it all.

The CHR's are the codes for separating your address lines onto
different lines in your text field - they're the codes for carriage
return and line feed.

Using the + instead of the & to concatenate will ensure that you'll
only get another line if the next piece of Address isn't null.

Hope this gets you started,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com