How Create a Formatted Address from Address Elements

[David40]

Would appreciate some help in the form of an example or a referral to a tutorial or something of that sort.

I need to create a formatted address field from a number of elements. The elements with sample data are as follows:

House_Number, 1234

House_Number_Suffix, 1/2

Street_Direction, East

Street_Name, Data

Street_Type Avenue

Street_Direction_Suffix, West

Unit_Type, Bldg. A

Apartment_Number 206

An example using all the elements combined in a single formatted field would be:

1234-1/2 East Data Avenue West Bldg A Apt.206

Thanks for any direction on how to do this.

David

[LaRetta]

Is there always a comma between the field descriptor and the data (there isn't in your example). If there isn't always a comma, do the field descriptors always remain the same? If so and there is no data, does the field descriptor list anyway but with no result?

Does this multiline element exist in one field or is it from several fields? If from several fields, why is the field description on the same line?

[David40]

Thanks for responding. My post got scrambled in the translation. I had separated the field descriptor from the sample data with a bunch of spaces since the Tab function does not work on the text here. When I posted my topic the system removed all my spaces and placed a comma in some while others it did not. The descriptors do remain the same. I just used this as an example of what I want to do I have not attempted it yet because I no nothing about joining a bunch of fields together as one with the spacing done correctly.

The end result needs to be just one line as a house address without the state, city, or zip.

[LaRetta]

Forgive me but I find this post terribly funny after another post from today. I refuse to answer your question on the grounds that, no matter what I say, I'll be contradicted (that's an inside joke not directed towards you).

Examples of concatenation

[David40]

Thanks, I'll have to study that thread.

Update

I read the other thread and using the Trimall function I believe the formula with my fields would look something like this:

TrimAll ( House_Number & " " & House_Number_Suffix & " " & Street_Direction & " " & Street_Name & " "& Street_Type & " " & Street_Direction_Suffix & " " & Unit_Type & " " & Apartment_Number ; 1 ; 1 )

Here is the big question. Now that I have a formula I believe will do the job, how do I make it go? I have a database with thousands of records and I basically need to apply this to the data one time so the formatted address field shows the formatted address for every record. What would be the simplest way to do this?

Thanks.

Edited April 11, 2009 by Guest

[bcooney]

Jumping in, I haven't reviewed the formula, but all you'd need to do is create another field, "dsp_AddrLabel"* , calc result text, and use this formula. Then put dsp_AddrLabel on your layout.

*dsp_ is a prefix I use for such "display" type fields. You can call it whatever you want.

Edit: In reviewing the formula, it puts all the data on one line, and that is what you want. But if you'd rather have it in a paragraph use

List(

House_Number;

House_Number_Suffix;

Street_Direction;

Street_Name;

Street_Type;

Street_Direction_Suffix;

Unit_Type;

Apartment_Number

)

Edited April 11, 2009 by Guest

[David40]

Yes, I have created that field already, I call it Formatted_Address.

It's all in my layout but I still don't know how to make it go. What is going to make it actually perform the calculation on all the existing records?

[LaRetta]

The new field should not be a standard field with an auto-enter calculation. It should be a field type calculation (look to the Type popup between Field Name and the Options button).

Then after you create the calculation, be sure the data type listed below (at the bottom of the calculation dialog box) is result of text and not number as it defaults. When you exit field definitions, the calculation will automatically index and update for all records in your table. :wink2:

[David40]

Thanks, I'll give that a try as soon as I can figure out where to find them.