VBA Code for replace function

[Purnima Sharma]

I have a table with a column called Properrty address and the Borrower's SSN.
I have to find the rows where SSN and property address are same. There is no
problem with SSN, however the property addresses are not consistent e.g in
one row it will be Chateau Drive, in other it will be Chateau Dr. How can I
use a replace function to convert St. into Street, DR. into Drive, Av. into
Avenue, St. into Street etc.
Can someone help?

Thanks!
Purnima Sharma

 

[fredg]

I have a table with a column called Properrty address and the Borrower's SSN.
I have to find the rows where SSN and property address are same. There is no
problem with SSN, however the property addresses are not consistent e.g in
one row it will be Chateau Drive, in other it will be Chateau Dr. How can I
use a replace function to convert St. into Street, DR. into Drive, Av. into
Avenue, St. into Street etc.
Can someone help?

Thanks!
Purnima Sharma

Unfortunately it's not quite as straight-forward as you think.
What about street names like St. Charles St., or Drury Dr., or entries
like Main St or Avenita del Norte Ave?
How does access know which St or Dr or Ave you wish to replace with
Street or Drive or Avenue?

Then, some city's add a direction after the street name, i.e. Elm St.
NW.

I'd suggest you take the time to separate the Ave, St, Ci, Pl, etc.,
into it's own field. Then it's a simple matter to update just that
field into Street, Avenue, Place, Circle, etc.

Update YourTable set YourTable.StreetField =
IIf(Left([StreetField],2)="St","Street",IIf Left(StreetField],2)=
"Av","Avenue",IIf( etc ....)))

 

[Klatuu]

You will never, ever get this 100% correct. fredg's observations are
correct. There is also the issue of misspellings.
Main st. Maine st. Mane st.

Several years ago, I worked on a project for a home delivery food service
where we had to do this to find an address in a database based on the call
taker's entry. We even had a book from the postal service that had
recommendations on how to store address, what parts of an address there are,
and ways to do address matching. It never worked perfectly. There are too
many varialbes.
Our solution was to use soundex coding and fuzzy logic and present the user
with a list of possible matches, but that may not even work in your case.

Best of Luck.

 

[John Nurick]

There's sample code here
http://www.j.nurick.dial.pipex.com/Code/vbRegex/ParseAddress36.htm
that shows how street addresses can be split into their component parts.

I have a table with a column called Properrty address and the Borrower's SSN.
I have to find the rows where SSN and property address are same. There is no
problem with SSN, however the property addresses are not consistent e.g in
one row it will be Chateau Drive, in other it will be Chateau Dr. How can I
use a replace function to convert St. into Street, DR. into Drive, Av. into
Avenue, St. into Street etc.
Can someone help?

Thanks!
Purnima Sharma

Unfortunately it's not quite as straight-forward as you think.
What about street names like St. Charles St., or Drury Dr., or entries
like Main St or Avenita del Norte Ave?
How does access know which St or Dr or Ave you wish to replace with
Street or Drive or Avenue?

Then, some city's add a direction after the street name, i.e. Elm St.
NW.

I'd suggest you take the time to separate the Ave, St, Ci, Pl, etc.,
into it's own field. Then it's a simple matter to update just that
field into Street, Avenue, Place, Circle, etc.

Update YourTable set YourTable.StreetField =
IIf(Left([StreetField],2)="St","Street",IIf Left(StreetField],2)=
"Av","Avenue",IIf( etc ....)))

 

[Klatuu]

That code will miss a lot. It does not conform to USPS address naming
conventions. It does not have all the possible prefixes and suffixes
identified. And even if it did, it would still not do a 100% match. There
are some addresses that just can't be parsed. Real life example from an
address matching project: In Houston, Texas, there is a street named West
Loop South

West is a prefix, South is a suffix, and Loop is a street type. It would
always come up with no street name.

There's sample code here
http://www.j.nurick.dial.pipex.com/Code/vbRegex/ParseAddress36.htm
that shows how street addresses can be split into their component parts.

I have a table with a column called Properrty address and the Borrower's SSN.
I have to find the rows where SSN and property address are same. There is no
problem with SSN, however the property addresses are not consistent e.g in
one row it will be Chateau Drive, in other it will be Chateau Dr. How can I
use a replace function to convert St. into Street, DR. into Drive, Av. into
Avenue, St. into Street etc.
Can someone help?

Thanks!
Purnima Sharma

Unfortunately it's not quite as straight-forward as you think.
What about street names like St. Charles St., or Drury Dr., or entries
like Main St or Avenita del Norte Ave?
How does access know which St or Dr or Ave you wish to replace with
Street or Drive or Avenue?

Then, some city's add a direction after the street name, i.e. Elm St.
NW.

I'd suggest you take the time to separate the Ave, St, Ci, Pl, etc.,
into it's own field. Then it's a simple matter to update just that
field into Street, Avenue, Place, Circle, etc.

Update YourTable set YourTable.StreetField =
IIf(Left([StreetField],2)="St","Street",IIf Left(StreetField],2)=
"Av","Avenue",IIf( etc ....)))

 

[Douglas J. Steele]

We have an Avenue Road here in Toronto, a fairly major street. <g>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That code will miss a lot. It does not conform to USPS address naming
conventions. It does not have all the possible prefixes and suffixes
identified. And even if it did, it would still not do a 100% match.
There
are some addresses that just can't be parsed. Real life example from an
address matching project: In Houston, Texas, there is a street named West
Loop South

West is a prefix, South is a suffix, and Loop is a street type. It would
always come up with no street name.

There's sample code here
http://www.j.nurick.dial.pipex.com/Code/vbRegex/ParseAddress36.htm
that shows how street addresses can be split into their component parts.

On Fri, 20 Oct 2006 12:42:01 -0700, Purnima Sharma wrote:

I have a table with a column called Properrty address and the
Borrower's SSN.
I have to find the rows where SSN and property address are same.
There is no
problem with SSN, however the property addresses are not consistent
e.g in
one row it will be Chateau Drive, in other it will be Chateau Dr. How
can I
use a replace function to convert St. into Street, DR. into Drive, Av.
into
Avenue, St. into Street etc.
Can someone help?

Thanks!
Purnima Sharma

Unfortunately it's not quite as straight-forward as you think.
What about street names like St. Charles St., or Drury Dr., or entries
like Main St or Avenita del Norte Ave?
How does access know which St or Dr or Ave you wish to replace with
Street or Drive or Avenue?

Then, some city's add a direction after the street name, i.e. Elm St.
NW.

I'd suggest you take the time to separate the Ave, St, Ci, Pl, etc.,
into it's own field. Then it's a simple matter to update just that
field into Street, Avenue, Place, Circle, etc.

Update YourTable set YourTable.StreetField =
IIf(Left([StreetField],2)="St","Street",IIf Left(StreetField],2)=
"Av","Avenue",IIf( etc ....)))

 

[Klatuu]

Same issue.

Toronto? For some reason, I thought you were in Edmonton.

We have an Avenue Road here in Toronto, a fairly major street. <g>

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

That code will miss a lot. It does not conform to USPS address naming
conventions. It does not have all the possible prefixes and suffixes
identified. And even if it did, it would still not do a 100% match.
There
are some addresses that just can't be parsed. Real life example from an
address matching project: In Houston, Texas, there is a street named West
Loop South

West is a prefix, South is a suffix, and Loop is a street type. It would
always come up with no street name.

There's sample code here
http://www.j.nurick.dial.pipex.com/Code/vbRegex/ParseAddress36.htm
that shows how street addresses can be split into their component parts.


On Fri, 20 Oct 2006 12:42:01 -0700, Purnima Sharma wrote:

I have a table with a column called Properrty address and the
Borrower's SSN.
I have to find the rows where SSN and property address are same.
There is no
problem with SSN, however the property addresses are not consistent
e.g in
one row it will be Chateau Drive, in other it will be Chateau Dr. How
can I
use a replace function to convert St. into Street, DR. into Drive, Av.
into
Avenue, St. into Street etc.
Can someone help?

Thanks!
Purnima Sharma

Unfortunately it's not quite as straight-forward as you think.
What about street names like St. Charles St., or Drury Dr., or entries
like Main St or Avenita del Norte Ave?
How does access know which St or Dr or Ave you wish to replace with
Street or Drive or Avenue?

Then, some city's add a direction after the street name, i.e. Elm St.
NW.

I'd suggest you take the time to separate the Ave, St, Ci, Pl, etc.,
into it's own field. Then it's a simple matter to update just that
field into Street, Avenue, Place, Circle, etc.

Update YourTable set YourTable.StreetField =
IIf(Left([StreetField],2)="St","Street",IIf Left(StreetField],2)=
"Av","Avenue",IIf( etc ....)))

 

[Douglas J. Steele]

That would be Albert.

 

[John Nurick]

That code will miss a lot.

Agreed; that's why I only offer it as a sample. But the regex can be
modified to handle more addresses.

It does not conform to USPS address naming
conventions.

It does not have all the possible prefixes and suffixes
identified. And even if it did, it would still not do a 100% match. There
are some addresses that just can't be parsed. Real life example from an
address matching project: In Houston, Texas, there is a street named West
Loop South

West is a prefix, South is a suffix, and Loop is a street type. It would
always come up with no street name.

The sample regex doesn't handle "West Loop South" but it's no big deal
to parse provided "Loop" is in the list of street types: given
(North|South|East|West) (street type)
it's a safe assumption that the compass point is a street name and not a
modifier.

OTOH I think "West Avenue Road" is impossible without bringing external
knowledge to bear: is it (West Avenue) (Road) or (West) (Avenue Road)?