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
Agree 100%. People have made careers of parsing name and address info.
We used to call it that famous IBM utility-- iehIBALL
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
read all the responses, not just the one you have been advised of - see post#11I 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.
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
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.
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
Michael, glad to help. Nice challenge and successful results.