Help Formatting Addresses in a Text Field within a table (1 Viewer)

[Fanflame]

Hi there,

I have been using Access for several years but always in a basic way. I have started work on an existing Members database where the Members Addresses have been pasted in from Notepad or been imported from csv or manual entry.

The ones that have been manually entered are on one line. The ones that are imported from csv or pasted from Notepad are on multiple lines like you would naturally type on an envelope...

In Notepad they look like this:

'Oakdale' 123 Expression Road
Name of Town
Name of County
Postcode

Sometimes the addresses have 4 lines and sometime they also have the name of a village which makes it 5 or 6 lines. When imported into Access they also view (datasheet view) in the same way with the return at the end of each line. However, I cannot enter a new address in this manner directly into Access.

How can I enter the address text into a text filed and be able to format it so that it looks like the above?

I have searched many times for an answer to this but cannot find a way of explaining it that search engines understand. I first came across this 12 years ago when I worked on a massive Members database for a radio station all the addresses where formatted in the same way and had been imported through csv. When I do this the csv file marks up the text as "'Oakdale' 123 Expression Road□Name of Town□Name of County□Postcode"

I have tried exporting to csv ad then importing it into GMAIL and this works fine I have also printed off labels and envelopes and all the addresses print fine too; I just don't know how to input it in that format other than pasting all addresses through a basic text file.

Many thanks for your help and practical suggestions.

At present I am still using Access 2003 - I have got 2007 but didn't really like it so switch back and stayed there!

fanflame

 

[jdraw]

Ideally you would have a standard definition for Addresses. Each of those address "records" that don't match your standard would require editing/handling to put them into the standard format.

Many people over the years have struggled with consistency in addresses. I worked on a project years ago that received/purchased names and addresses from various sources and sent "personalized" tourism opportunities to these people. There was incredible variation in the formatting, spelling, quality of the information --both within a specific purchase and across/among various purchases.

There are commercial packages to assist the task; most are used by national post offices to standardize addresses and to barcode these for electronic processing of mail.

It's a major undertaking. Good luck with your project.

 

[RainLover]

'Oakdale' 123 Expression Road
Name of Town
Name of County
Postcode

All of the above need to be stored in separate fields within your table.

Any formatting should be done within the design of the Form and/or Report.

The way you have it now you can't do a simple filter or search on something like the Name of Town.

If you really are stuck and have to do it the way as shown try using a MEMO field. Not 100% sure it will work but worth a try.

 

[Fanflame]

Thanks to you both for your input.

About 12 years ago I first came upon this anomaly I thought that as the full address worked when producing a mail merge I would carry on doing it.

Having not worked on a large DB for some time but now taking on a new contract where the DB is quite small but some addresses as formatted as fully formatted addresses in a field called <<Address>> and some are in the traditional field of:
<<Address1>>, <<Address2>>, <<Address3>>, <<Town>>, <<County>>, <<Postcode>>

I would actually try and work out why you could format an address within the field with the keyboard return when pasting it from a text editor. some part of me thinks that this saves me time because often people send me their address in an email and I just cut and paste it once in the <<Address>> field.

I am using a Form to display Members information for the sales people and the Address field displays perfectly in there too. I was just wondering if I , or the team, should bother filling out any more of the other 6 fields. Still unsure what to do in the long run. I think I will wait and see what other people have to say or if they have come across this anomaly at all.

I would upgrade office to 2007 as I have the license to do so but despise the thought of paying out nearly 400 quid for the latest office product of which I do not like. I liked 2003 and the company I worked for for 10 years stayed on it and XP Pro through the vista/win7/8 changes.

Many thanks for all your suggestions.

 

[jdraw]

A key concept with database is to have atomic fields -- one field one fact. An Address is made up, as you have shown and know well,
<<Address1>>, <<Address2>>, <<Address3>>, <<Town>>, <<County>>,<<Province/State>>, <<Postcode>>, <<Country>>.

You can "assemble " these atomic fields into a group or groups as you need them. I would recommend you create an Address table with the atomic fields you need. I believe this will be the easiest to maintain with your various "address" inputs/sources.

 

[Fanflame]

Hi JDraw,

Thanks for your observations. I agree that this particular field <<Address>> is not atomic in any way as it holds way too much information. However I think that it has become this way because in the past previous people like myself have found that pasting a formatted address from a text file or even an email goes straight in as pasted; that then saves a little time. When the only searching that is done on the data has been postcodes it has not been difficult to do at all.

I think I will create the relative address line fields for the sake of convention whilst the database is still small.

Many thanks for your help.

Fanflame