making new field that combines three other fields?

[ND]

I need to create a separate field from 4 fields, "street address", "city",
"State" and "zip code". For example,

Street address - 100 Forest Street
City - Seattle
State - WA
Zip - 05555

Would become:

100 Forest Street, Seattle, WA, 05555

Any idea how to do this?

Thanks.

[fredg]

On Sat, 24 Jul 2004 12:21:28 -0500, ND wrote:

I need to create a separate field from 4 fields, "street address", "city",
"State" and "zip code". For example,

Street address - 100 Forest Street
City - Seattle
State - WA
Zip - 05555

Would become:

100 Forest Street, Seattle, WA, 05555

Any idea how to do this?

Thanks.

regarding: >I need to create a separate field from 4 fields<

If by "separate field" you mean a new field in a table to store the
combined data, no you don't. Derived data, such as this, should not be
stored in any table. If the value of one of the entries, such as
[StreetAddress] is changed, then you no longer have correct values in
this combined field.

If however, you mean to put the fields together in an unbound (not
tied to a table) control in a form or report, then that is easily
done.

Add an unbound control to the report.
To display the data as
100 Forest Street, Seattle, WA, 05555
set it's Control Source to:

=[StreetAddress] & ", " & [City] &", " & [State] & ", " & [Zip]
To combined the fields into a proper mailing address format,
100 Forest Street
Seattle, WA 05555
use:

=[StreetAddress] & chr(13) & chr(10) & [City] &", " & [State] & " " &
[Zip]

But don't save the combined address data! It's not necessary.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[ND]

fredg <fg******@examp le.invalid> wrote in
news:c3******** *************** ******@40tude.n et:

On Sat, 24 Jul 2004 12:21:28 -0500, ND wrote:

I need to create a separate field from 4 fields, "street address",
"city", "State" and "zip code". For example,

Street address - 100 Forest Street
City - Seattle
State - WA
Zip - 05555

Would become:

100 Forest Street, Seattle, WA, 05555

Any idea how to do this?

Thanks.
regarding: >I need to create a separate field from 4 fields<

If by "separate field" you mean a new field in a table to store the
combined data, no you don't. Derived data, such as this, should not be
stored in any table. If the value of one of the entries, such as
[StreetAddress] is changed, then you no longer have correct values in
this combined field.

I actually do need to merge this data into a single field. I'm importing
the information into ArcGIS for determining the geographic location of
addresses on a map and the format they require merges all that data
together. We will continue to enter data manually into the original
separated fields but I can either create a merged field to do this
transformation automatically or I can manually merge the data together
(which won't happen). Is there a way to define a field using the format
below so that the field will update itself? Thanks.

If however, you mean to put the fields together in an unbound (not
tied to a table) control in a form or report, then that is easily
done.

Add an unbound control to the report.
To display the data as
100 Forest Street, Seattle, WA, 05555
set it's Control Source to:

=[StreetAddress] & ", " & [City] &", " & [State] & ", " & [Zip]
To combined the fields into a proper mailing address format,
100 Forest Street
Seattle, WA 05555
use:

=[StreetAddress] & chr(13) & chr(10) & [City] &", " & [State] & " " &
[Zip]

But don't save the combined address data! It's not necessary.

[fredg]

On Sat, 24 Jul 2004 13:08:11 -0500, ND wrote:

fredg <fg******@examp le.invalid> wrote in
news:c3******** *************** ******@40tude.n et:

On Sat, 24 Jul 2004 12:21:28 -0500, ND wrote:

I need to create a separate field from 4 fields, "street address",
"city", "State" and "zip code". For example,

Street address - 100 Forest Street
City - Seattle
State - WA
Zip - 05555

Would become:

100 Forest Street, Seattle, WA, 05555

Any idea how to do this?

Thanks.

regarding: >I need to create a separate field from 4 fields<

If by "separate field" you mean a new field in a table to store the
combined data, no you don't. Derived data, such as this, should not be
stored in any table. If the value of one of the entries, such as
[StreetAddress] is changed, then you no longer have correct values in
this combined field.

I actually do need to merge this data into a single field. I'm importing
the information into ArcGIS for determining the geographic location of
addresses on a map and the format they require merges all that data
together. We will continue to enter data manually into the original
separated fields but I can either create a merged field to do this
transformation automatically or I can manually merge the data together
(which won't happen). Is there a way to define a field using the format
below so that the field will update itself? Thanks.

If however, you mean to put the fields together in an unbound (not
tied to a table) control in a form or report, then that is easily
done.

Add an unbound control to the report.
To display the data as
100 Forest Street, Seattle, WA, 05555
set it's Control Source to:

=[StreetAddress] & ", " & [City] &", " & [State] & ", " & [Zip]

To combined the fields into a proper mailing address format,
100 Forest Street
Seattle, WA 05555
use:

=[StreetAddress] & chr(13) & chr(10) & [City] &", " & [State] & " " &
[Zip]

But don't save the combined address data! It's not necessary.

A couple of ways you can do this.

1) Run an update query to concatenate the separate fields into one
field:
Add a new field to the table ... "FullAddres s".
then create an update query:

Update YourTableName Set YourTableName.F ullAddress = [Address] & ", "
& [City] & ", " & ", " & [Zip];

The above is best for existing data.

2) For new data, add the "FullAddres s" field to the table.
Then use a form to enter the data into the separate fields as you do
now.
Add the [FullAddress] field to the form. (You can make it not visible
if you don't want to actually see it.)

Code the Form's BeforeUpdate event:
[FullAddress] = [StreetAddress] & ", " & [City] &", " & [State] & ", "
& [Zip]

The FullAddress data will be added to the table when you navigate to
the next record, or save the current record.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

[ND]

fredg <fg******@examp le.invalid> wrote in
news:at******** *************** ******@40tude.n et:

On Sat, 24 Jul 2004 13:08:11 -0500, ND wrote:

fredg <fg******@examp le.invalid> wrote in
news:c3******** *************** ******@40tude.n et:

On Sat, 24 Jul 2004 12:21:28 -0500, ND wrote:

I need to create a separate field from 4 fields, "street address",
"city", "State" and "zip code". For example,

Street address - 100 Forest Street
City - Seattle
State - WA
Zip - 05555

Would become:

100 Forest Street, Seattle, WA, 05555

Any idea how to do this?

Thanks.

regarding: >I need to create a separate field from 4 fields<

If by "separate field" you mean a new field in a table to store the
combined data, no you don't. Derived data, such as this, should not
be stored in any table. If the value of one of the entries, such as
[StreetAddress] is changed, then you no longer have correct values
in this combined field.

I actually do need to merge this data into a single field. I'm
importing the information into ArcGIS for determining the geographic
location of addresses on a map and the format they require merges all
that data together. We will continue to enter data manually into the
original separated fields but I can either create a merged field to
do this transformation automatically or I can manually merge the data
together (which won't happen). Is there a way to define a field using
the format below so that the field will update itself? Thanks.

If however, you mean to put the fields together in an unbound (not
tied to a table) control in a form or report, then that is easily
done.

Add an unbound control to the report.
To display the data as
100 Forest Street, Seattle, WA, 05555
set it's Control Source to:

=[StreetAddress] & ", " & [City] &", " & [State] & ", " & [Zip]

To combined the fields into a proper mailing address format,
100 Forest Street
Seattle, WA 05555
use:

=[StreetAddress] & chr(13) & chr(10) & [City] &", " & [State] & " "
& [Zip]

But don't save the combined address data! It's not necessary.

A couple of ways you can do this.

1) Run an update query to concatenate the separate fields into one
field:
Add a new field to the table ... "FullAddres s".
then create an update query:

Update YourTableName Set YourTableName.F ullAddress = [Address] & ", "
& [City] & ", " & ", " & [Zip];

The above is best for existing data.

2) For new data, add the "FullAddres s" field to the table.
Then use a form to enter the data into the separate fields as you do
now.
Add the [FullAddress] field to the form. (You can make it not visible
if you don't want to actually see it.)

Code the Form's BeforeUpdate event:
[FullAddress] = [StreetAddress] & ", " & [City] &", " & [State] & ", "
& [Zip]

The FullAddress data will be added to the table when you navigate to
the next record, or save the current record.

Thank you much. I appreciate the help.

[Larry Daugherty]

Hi ND,

Another comment from 'way out in the bleachers: you wrote that you were
importing the data into ArcGIS. What's the current mechanism? How flexible
can you be about how the data gets where? I share Fred's aversion to
storing the merged field in the database; gross violation of relational
rules don'cha know.

You can easily create a separate table with all of the other data and just
the merged field form of the address without the fields with the individual
bits. You can use a Make Table query or you can run a Make Table once and
then use code to run a Delete query to empty the new table and an Append
query to fill it again.

The query would look much as it does now but you would click off the Show
checkbox for the fields that will be merged. You can create a new merged
data field by treating the data similarly to what Fred has shown.
MergedData: =[StreetAddress] & ", " & [City] &", " & [State] & ", " & [Zip]
Your new field is MergedData and has the goodies.

If it were mine to do I'd want that data outside my application, maybe in an
Excel spreadsheet or in a CSV file.

HTH
--
-Larry-
--

"ND" <[e-mail] .invalid> wrote in message
news:Xn******** *************** *********@216.1 96.97.131...

fredg <fg******@examp le.invalid> wrote in
news:at******** *************** ******@40tude.n et:

On Sat, 24 Jul 2004 13:08:11 -0500, ND wrote:

fredg <fg******@examp le.invalid> wrote in
news:c3******** *************** ******@40tude.n et:

On Sat, 24 Jul 2004 12:21:28 -0500, ND wrote:

> I need to create a separate field from 4 fields, "street address",
> "city", "State" and "zip code". For example,
>
> Street address - 100 Forest Street
> City - Seattle
> State - WA
> Zip - 05555
>
> Would become:
>
> 100 Forest Street, Seattle, WA, 05555
>
> Any idea how to do this?
>
> Thanks.

regarding: >I need to create a separate field from 4 fields<

If by "separate field" you mean a new field in a table to store the
combined data, no you don't. Derived data, such as this, should not
be stored in any table. If the value of one of the entries, such as
[StreetAddress] is changed, then you no longer have correct values
in this combined field.

I actually do need to merge this data into a single field. I'm
importing the information into ArcGIS for determining the geographic
location of addresses on a map and the format they require merges all
that data together. We will continue to enter data manually into the
original separated fields but I can either create a merged field to
do this transformation automatically or I can manually merge the data
together (which won't happen). Is there a way to define a field using
the format below so that the field will update itself? Thanks.

If however, you mean to put the fields together in an unbound (not
tied to a table) control in a form or report, then that is easily
done.

Add an unbound control to the report.
To display the data as
100 Forest Street, Seattle, WA, 05555
set it's Control Source to:

=[StreetAddress] & ", " & [City] &", " & [State] & ", " & [Zip]

To combined the fields into a proper mailing address format,
100 Forest Street
Seattle, WA 05555
use:

=[StreetAddress] & chr(13) & chr(10) & [City] &", " & [State] & " "
& [Zip]

But don't save the combined address data! It's not necessary.

A couple of ways you can do this.

1) Run an update query to concatenate the separate fields into one
field:
Add a new field to the table ... "FullAddres s".
then create an update query:

Update YourTableName Set YourTableName.F ullAddress = [Address] & ", "
& [City] & ", " & ", " & [Zip];

The above is best for existing data.

2) For new data, add the "FullAddres s" field to the table.
Then use a form to enter the data into the separate fields as you do
now.
Add the [FullAddress] field to the form. (You can make it not visible
if you don't want to actually see it.)

Code the Form's BeforeUpdate event:
[FullAddress] = [StreetAddress] & ", " & [City] &", " & [State] & ", "
& [Zip]

The FullAddress data will be added to the table when you navigate to
the next record, or save the current record.

Thank you much. I appreciate the help.