Combine City State, Zip into one display field

[buratti]

I have a field on my form that I need to combine my [city] [state] and [zip] fields into one field, BUT ALSO make that field editable. I already know how to simply combine those fields using =[city]&" "&[State]&", "&[Zip] in the control source, but although that displays everything in the correct format, I need to be able to edit it also.

So for example if the field were to display "New York NY, 10011" and I wanted to change it to "Manhattan NY, 10012" I could just type it into the field and it would update the apropriate fields.

Any suggestions???

 

[jdraw]

Since the Form can display values from a table, are you sayiing that you want to be able to update the data in the underlying table?

In many cases where you are building addresses,you can use a table that relates zip/postal codes to State/Prov, City, Street etc. Don't know if it applies to you, but thought I should mention it.

 

[plog]

My first and least favorite way to accomplish this is to make that an unbound control, use VBA code to load those 3 fields data into it, then even write some abominable VBA code to try, and I emphasize 'try', to parse whatever the user types into that control back into those 3 fields.

My second and still not recommended way of doing this is to denormalize your data and make that just one field. Instead of city, state and zip fields it would become one citystatezip field. This is an equally bad idea, but a little easier to implement than the VBA code method.

The correct way to do this, is to not. Since each of those is a discrete piece of data, they should be in 3 distinct controls.

 

[buratti]

Yes in the underlying table I have 3 seperate fields [city] [state] and [zip], and combine them to "view" as 1 field. I want to edit that 1 field and have the 3 underlying fields updated correctly. How would I go about having the table "realize" the addresses as you suggested?

 

[Mr. B]

While I feel sure that code could eventually be written that would be able to read a user's entry in the text box and extract the various field values, this would be somewhat tricky if you do not put some type of input mask on the text box so you will know the format for entering the values is met every time.

For example, normally when all three of these value are entered together, I see City, State and Zip Code entered as "New York, NY 10011" but even here in your post, you entered this as: "New York NY, 10011" putting the comma after the state, not after the City.

This type of thing will make it very time consuming to write a function that will correctly read the three field values and get the correct every time. Again, I feel sure that given enough time it can be done, but it would take a while to make sure ever base was covered.

I would suggest that you might look at some other method for allowing the user to update the information and just display the concatenated value.

Just my two cents.