Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22

    Is this even possible?


    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

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    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".

  4. #4
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    Thank you so much guys. This works perfectly.

    Michael

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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

  6. #6
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    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.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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

  8. #8
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    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

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Attachment 25246

    This may give you ideas on fine control.

  10. #10
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    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

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can identify the last word using

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

    will return 'Road' from '123 Ridge Road'

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ....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?

  13. #13
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    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

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    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.

  15. #15
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    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)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums