Address Scrubber

[dcreswell]

Hello:

Do any of you know if there is a vb or vba routine out there I can use to standardize an address to postal standards? For instance, something that would make P O Box, P.O. Box, Post Office Box, and Box all read PO Box?

Thanks,
David Creswell

 

[ZmrAbdulla]

You may consider "InStr Function" and "Replace Function" to create one. for more see vba help file

Or an "update query" will update it for ever.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[missinglinq]

Zameer Abdulla has the right idea, but I've got to tell you that at best this is going to be a daunting task. The example you gave is just that, one example. Then you've got all the abbreviations for "street", "avenue" and "boulevard" to say nothing of "trail" "trace" and "turnpike" (all of which, at times, are abbreviated "tr" to consider! Once upon a time, in another life, I moonlighted for a temp agency doing data conversion. The client was one of the three largest banks in the world, so you can imagine the size and talent involved in their IT department. Upon acquiring two smaller banking corporations, they spent months trying to convert the purchased banks' address schemes to comply with their own software. They finally concluded that the most cost effective way to do the conversion was to hire a temp agency with dozens of data entry geeks to manually do the conversions!

Good luck!

The Missinglinq

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya dcreswell . . .

Perhaps this function:

[blue]Public Function POCheck(Adr As String) As String

   If InStr(1, Adr, "P O Box") Then
      POCheck = Replace(Adr, "P O Box", "PO Box")
   ElseIf InStr(1, Adr, "P.O. Box") Then
      POCheck = Replace(Adr, "P.O. Box", "PO Box")
   ElseIf InStr(1, Adr, "Post Office Box") Then
      POCheck = Replace(Adr, "Post Office Box", "PO Box")
   Else
      POCheck = Adr
   End If

End Function[/blue]

See Ya! . . . . . .

 

[DomFino]

Hi guys,
You may want to check out this site:

http://global.dymo.com/enUS/Campaigns/Address_Fixer_Main_Download.html

It is free address fixer that offered by Dymo Label. This took automatically checks addressed against the US Post office database. I have used it to correct address in a maile merge program some time ago. I also use it to correct addressess directly in Word. I have not figured out how to use it directly from Access but someone guru on this site may be able to do that.
Dom