Page 2 of 2 FirstFirst 12
Results 16 to 23 of 23
  1. #16
    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


    Quote Originally Posted by davegri View Post
    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.


    And then, of course, the address can be something like 13 Blue Moon Crescent, NE 10...where the penultimate element is NE, which adds even more complexity to the parsing!

    In the end, it's got to be EBBAP'd (EyeBalled By A Person)...for total compliance!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  2. #17
    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
    Agree 100%. People have made careers of parsing name and address info.

    We used to call it that famous IBM utility-- iehIBALL

  3. #18
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    That's what we have been doing more or less. The way we've been cleaning addresses is to Paste them into Excel, do an text to columns and then sort from Right to Left one column at a time until all have been fixed into the standard USPS format. It works but takes a lot of time but does result in nearly perfect compliant addresses. I've been playing with the above code today and to my surprise, it works in 99.9% of the issues we were having. I added all the various scenarios for address suffix's and it has caught them all. But I would like to find a way to doing in an UPDATE SQL rather than a VBA script simply because we are talking about 10 to 20K of addresses a week and an Update SQL would make it fast. As for the leading directional issues using this script, I changed the script to find the first word if its North, South, Etc. The problem with that is we have to run the addresses twice through the form. But still a considerable time saver. What it didn't handle at least initially is misspellings such as ROA instead of ROAD. But I put the common misspellings we've run into the code and it has caught them as well. we will still need to continue doing some eyes on but so far things are looking very promising.

    I would like to know if what I want to do in SQL is even possible..ie find the last word if it matches a word and then replace it with another word.

    Thanks for you input. I really do appreciate it.

    Michael

  4. #19
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I would like to know if what I want to do in SQL is even possible..ie find the last word if it matches a word and then replace it with another word.
    read all the responses, not just the one you have been advised of - see post#11

  5. #20
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    I'm so sorry Ajax! I completely missed your response. Let me work on it an see if I can make it work but from what "little" I know, it looks workable. Thanks for your reply and your time! I'll post back when I see what I can do with it.

    Michael

  6. #21
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Attachment 25257

    OK. Modified code so that you don't need to scroll through all the records to make the changes via the form's Current_Event.
    Added a button to the form that will read the entire file and make appropriate updates. It's not SQL, but it is FAST and still allows you to fine tune the conversions.
    The form will allow you to scroll thru the records to view the changes, but any changes via the form will be manual.

  7. #22
    autiger58 is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2015
    Posts
    22
    You, davgri, are amazing! Thank you so very much. This does exactly what I wanted to do. Thanks for taking your time to help me.

    Take care, Michael

  8. #23
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Michael, glad to help. Nice challenge and successful results.

Page 2 of 2 FirstFirst 12
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