[AccessD] Address Recognition

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
>





More information about the AccessD mailing list