Concatnate Two Fields

[AnonymousAddressField]

There are two Address fields in an ACCESS table with
thousands of records, Addr1 and Addr2. I want to combine
Addr2 into Addr1. How do I do accomplish?

TIA

 

[Mike Webb]

Select [yourtable.Addr1] & ' ' & [yourtable.Addr2] AS NewAddressFieldName
FROM yourtable

That should work. It will combine the two fields into a new one called (in
this example) NewAddressFieldName, and the two will be separated by a space.

HTH,
Mike

 

[John Vinson]

There are two Address fields in an ACCESS table with
thousands of records, Addr1 and Addr2. I want to combine
Addr2 into Addr1. How do I do accomplish?

TIA

BACK UP YOUR DATABASE first, lest this go awry!

I'm *assuming*, perhaps erroneously, that you have addresses like

338 Wilshire Blvd
Suite 103

and that you want to concatenate them onto a single line:

338 Wilshire Blvd Suite 103

If so, create an Update query based on your table; on the Update To
line under Addr1 put

[Addr1] & " " & [Addr2]

If you want a comma between them, and if there are some without
anything in Addr2, you can use

Addr1 & (" " + [Addr2])

This sneakily uses the fact that the + operator concatenates strings,
but that if either string is NULL it returns NULL; the & operator
treats a NULL as an empty string "".

Finally... consider whether you really want to do this. Having the
fields separately gives you the flexibility to display them on two
lines as in my first example, *or* to concatenate them in a query;
once you've combined them it's much harder to get them apart.

 

[Steve Schapel]

AnonymousAddressField,

To add to the suggestions of Mike and John, if you are trying to get the
two address fields to show on 2 lines one under the other, to show as
such on form or report, then...
FullAddress: [Addr1] & Chr(13) & Chr(10) & [Addr2]

 

[AnonymousAddessField]

Thanks to all, its time to write the code.

Thanks....

-----Original Message-----
AnonymousAddressField,

To add to the suggestions of Mike and John, if you are trying to get the
two address fields to show on 2 lines one under the other, to show as
such on form or report, then...
FullAddress: [Addr1] & Chr(13) & Chr(10) & [Addr2]

--
Steve Schapel, Microsoft Access MVP

There are two Address fields in an ACCESS table with
thousands of records, Addr1 and Addr2. I want to combine
Addr2 into Addr1. How do I do accomplish?

TIA

.