Michael Bahr
jedi at charm.net
Sun Jun 17 20:09:30 CDT 2007
Thanks for the reference Marty. Reuben, sorry for not supplying a complete example I am at home. I know that if you open your db and open the code part you can search for "regular expression" and get some results. I got my information on how to use it here http://www.regular-expressions.info/vbscript.html Also I highly recommend the book, Mastering Regular Expressions http://www.oreilly.com/catalog/regex3/ it is the bible for regular expressions. You do not want to leave home without it! :-O I will look at work and find some examples to pass on to you. Mike... > Here is a mdb, I wrote awhile back for Access 97 to footle around > with Regular expressions. With 97 , you had to force a reference but > in 2000 you just need a reference to vbscript.dll to get version 5.5 or > 5.6 > andalso remove the startup code to force a reference. Version 5.5 has > extra > objects to handle Multiline or SubExpressions. > > See text files. > > > http://www.rogersaccesslibrary.com/OtherLibraries.asp > Regular Expression Validator (RegularExpressionValidator.mdb) (92 > KB) Access 97 > > > From the documentor method within above mbd here is what the > expressions do. > > ^(\d+\s+[a-zA-Z]+\s+([.a-zA-Z]+)?) > > Start of input > Followed by Pattern-1: ( > digit one or more times > Followed by whitespace one or more times > Followed by any of a, -, z, A, -, Z one or more times > Followed by whitespace one or more times > Followed by Pattern-2: ( > any of ., a, -, z, A, -, Z one or more times > )-end-Pattern-2 zero or one times > )-end-Pattern-1 > > > ^([/0-9\s]+\s+[a-zA-Z]+\s+([.a-zA-Z]+)?) > > Start of input > Followed by Pattern-1: ( > any of /, 0, -, 9, whitespace one or more times > Followed by whitespace one or more times > Followed by any of a, -, z, A, -, Z one or more times > Followed by whitespace one or more times > Followed by Pattern-2: ( > any of ., a, -, z, A, -, Z one or more times > )-end-Pattern-2 zero or one times > )-end-Pattern-1 > > Reuben Cummings wrote: > >>That looks really cool. However, I have to admit, I don't understand a >> lick >>of it. >> >>What in the world do I do with it? >> >>Reuben Cummings >>GFC, LLC >>812.523.1017 >> >> >> >> >>>-----Original Message----- >>>From: accessd-bounces at databaseadvisors.com >>>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Michael Bahr >>>Sent: Saturday, June 16, 2007 10:22 PM >>>To: Access Developers discussion and problem solving >>>Subject: Re: [AccessD] Address Recognition >>> >>> >>>Hi Reuben, your friend could be regular expressions. Here is something >>>that would get most of your street address: >>> >>>^(\d+\s+[a-zA-Z]+\s+([.a-zA-Z]+)?) >>> >>>^ = start of string, this is optional >>>\d+ = 1 or more digits >>>\s+ = 1 or more spaces >>>[a-zA-Z]+ = 1 or more alpha chars >>>([a-zA-Z]+)? 1 or more alpha char **maybe** >>>the [] creates a class of characters to match >>> >>>the parenthsis at the start and end capture the whole matching string >>> and >>>saves it a global read-only variable you can dereference. If you want >>> to >>>capture 485 1/2 you try relpacing the \d+ with something like: >>> >>>[/0-9\s]+ >>> >>>hth, Mike... >>> >>> >>> >>>>I have an app that loads BMV records from the state. In those >>>> >>>> >>>records are >>> >>> >>>>the auto owners home addresses. Unfortunately, the addresses are >>>> listed >>>>completely in one field. For example 485 1/2 Ferry St or 676 Ferry >>>>Street. >>>> >>>>I need to go thru this entire list upon importing from the state and >>>> set >>>>every record to its proper taxing unit which is entirely based upon the >>>>address listed. If no address is listed I will simply indicate >>>> >>>> >>>the taxing >>> >>> >>>>unit as Unknown and the user will check manually. >>>> >>>>My biggest problem is illustrated by the following address >>>> >>>> >>>(real data from >>> >>> >>>>a >>>>client)... >>>>91 Antioch >>>>60 Antioch Lane >>>>12 Antioch LN >>>>6 Antioch RD >>>>300 Antioch Road >>>> >>>>These may be one, two, or three different roads. Either way there are >>>> 5 >>>>variations that must be analyzed. >>>> >>>>Anyone have any good functions or suggestions for reading these >>>> >>>> >>>addresses >>> >>> >>>>and assigning units? >>>> >>>>I plan to make a table that will allow the user to define the >>>> >>>> >>>road names, >>> >>> >>>>address ranges, which sides of the road belong in a taxing unit. My >>>>problem >>>>is how do I handle all the variations of a road? Should I force them >>>> to >>>>simply add more road definitions or is there a way to "Very Accurately >>>>Guess" what road it should be? >>>> >>>>Also, how do I handle a road number like 485 1/2 Ferry St. >>>> >>>> >>>Val([Address]) >>> >>> >>>>returns 4851 which results in a Street Name being /2 Ferry St. >>>> >>>> >>>I need to >>> >>> >>>>analyze and handle the 1/2 portion of this address as well. >>>> >>>>I am currently working on this with a few counties, but plan to >>>> >>>> >>>meet with >>> >>> >>>>the state BMV to try to make it the default processing software for ALL >>>>counties in Indiana. >>>> >>>>Any and all help greatly appreciated! >>>> >>>>Reuben Cummings >>>>GFC, LLC >>>>812.523.1017 >>>> >>>> >>>> >>>>-- >>>>AccessD mailing list >>>>AccessD at databaseadvisors.com >>>>http://databaseadvisors.com/mailman/listinfo/accessd >>>>Website: http://www.databaseadvisors.com >>>> >>>> >>>> >>>-- >>>AccessD mailing list >>>AccessD at databaseadvisors.com >>>http://databaseadvisors.com/mailman/listinfo/accessd >>>Website: http://www.databaseadvisors.com >>> >>> >>> >> >> >> >> >> > > -- > Marty Connelly > Victoria, B.C. > Canada > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >