Is this even possible?

[autiger58]

Question.....I have a data base with address fields. Is it possible to write program (VBA or SQL) with an Update query that will correct addresses? What I mean is this...I have a address say...123 Main Street and I want to change Street to St. The same for Drive to Dr, Avenue to Ave, Etc. I have thousands that have to be corrected weekly and currently, we are exporting the data to excel, manually fixing the data and importing the corrected field back into Access. Does anyone know if this is possible and if so, can someone point me in a direction on getting started with the code?



Thanks,

Michael

[CJ_London]

use an update query and a nested replace function

something like

UPDATE tblAddresses
SET Address1=replace(replace(Address1," Street","St),Address1," Avenue","Ave"), Address2=replace(replace(Address2," Street","St),Address2," Avenue","Ave")

Note the space before Street etc - otherwise an address like 3 Brookstreet Close would become 3 Brookst Close

[John_G]

Note the space before Street etc - otherwise an address like 3 Brookstreet Close would become 3 Brookst Close

Just a small correction - if the string to be searched for has a space in front of it, like " Street", its replacement needs a space as well, like " St".

[autiger58]

Thank you so much guys. This works perfectly.

Michael

[orange]

autiger58,

I'm not sure where you are located or what addresses you are working with, but your postal service may have some standnards that you should be aware of. In Canada the street type and other standards are found here. see section 4

[autiger58]

From the US. We run the data (after we do some basic cleanup work) through a USPS database but it tends to be very temperamental with the data. The cleaner we can get the data before running it through that DB the less kick outs we have.

[CJ_London]

Just a small correction - if the string to be searched for has a space in front of it, like " Street", its replacement needs a space as well, like " St".

Good point

[autiger58]

Now I have another question that has come to light from using this code. In America, there are times where you might have something like 123 Main Street Road or 123 Ridge Road or 123 Ridge Road North. In the above example, the code changes change both Street and Road and Ridge, Road and North. Is there a way to ONLY have the code look at the last word in an address and only change that one in SQL unless the last word is S, N, W, E, SW, NW, NE, SE, South, North, East, West at which time it would go to the next of the last word? Is this possible?

Thanks, Michael

[davegri]

Attachment 25246

This may give you ideas on fine control.

[autiger58]

Thanks. Good Program. However it doesn't solve my problem. This program does what the SQL code does from above. I would like to do it in and SQL Update Query that looks at the last word of the address string and IF that word matches AVENUE, STREET, ROAD, PARKWAY,etc,then replace it with the correct postal code. I can code a select SQL to Find the last word and I've used the sql code above to find a specific word and replace it with another word..work great by the way! But how do I limit the word search to ONLY the last word in the address string?

Any ideas would be greatly appreciated.

Thank, Michael

[CJ_London]

you can identify the last word using

mid(myaddress,intstrrev(myaddress," ")+1)

will return 'Road' from '123 Ridge Road'

[orange]

....the last word of the address string and IF that word matches AVENUE, STREET, ROAD, PARKWAY,etc,then replace it with the correct postal code.

Please explain in simple terms how you would do this. What data do you have that we don't know about?

[autiger58]

UPDATE tblAddresses
SET Address1=replace(replace(Address1," Street","St"),Address1," Avenue","Ave"), Address2=replace(replace(Address2," Street"," St),Address2," Avenue"," Ave")

Here's my issue...on some addresses, you sometimes have two instances of two words that would be replaced using the above code..ie.....123 Ridge Road or 123 Peyton Place Lane. Setting the above code to replace all the various address suffix's, such as Road to RD, Ridge to Rdg, Place to PL, Street to St, Lane to Ln, etc. So if there are two instances such as 123 Ridge Road, the above code returns 123 Rdg Rd which is not expectable. But if the code would only check and change the last word in the address if it falls into one of the pre-defined search words and replacements that would solve a huge problem for me.

Is it possible to have the above code ONLY search the last word and not the whole string and only replace the last word if it is a one of the defined words that should be changed?

Michael

[davegri]

Attachment 25248

If the last (rightmost) address element is N,S,E,W,North,South, etc it will not be changed, but the previous element will be checked and changed Road to Rd., Street to St., etc.
If the last element is not one of the above, the last element will be checked and changed Road to Rd., Street to St. etc.
All other elements will not be changed.
You can add change comparisons to the function.

[Missinglinq]

Not to come across as a doomsayer, but just as a warning, you need to be aware that many, many top notch IT departments have addressed this problem with very little success!

If the nomenclature for this kind of thing was limited to the more common

Avenue
Drive
Highway
Lane
Road
Street

this kind of problem could come close to being doable, but the possibilities simply go on and on and on and on and on, making it nearly impossible to automate successfully!

While in school I did some contract work, through an agency, for a number of the biggest banks in the country. The scenario involved BankA purchasing BankB, and BankA needing to make the BankB addresses come into compliance with their own address formats. All of the BankAs were national, multi-billion dollar institutions, with tremendous IT departments, and none of them were able to automate the task enough to do even the simplest 'cleanup' reliably! The problem is that…in addition to the designations above…other commonplace designations include, but are not limited to:

Alley
Annex
Bottom
Boulevard
Bypass
Byway
Causeway
Circle
Close
Court
Crescent
Crossing
Freeway
Gardens
Gateway
Green
Heights
Hollow
Landing
Loop
Muse
Parkway
Pass
Passage
Pike
Place
Plaza
Ridge
Ring
Row
Square
Terrace
Trace
Trail
Viaduct
Way

As I said, these organizations had crackerjack IT teams, but in the end, after hundreds of hours of development time, every team ended up recommending the using of teams of data entry workers who simply eyeballed the original designations/abbreviations and manually made the needed changes!

Linq ;0)>