I have a table with an address field. The data in this field for example is
1234 Main St.; 1234 Main Street. I want the data to be consistent, example,
all "St. Should be "Street" and "Rd" should be "Road". Can anyone please
help me with a query that will change all adresses ending with "Rd" to
"Road" and so on?

Thanks.

On Sat, 10 Jul 2010 22:10:47 -0400, "Kissi Asiedu" <kissi5559 (AT) aol (DOT) com>
wrote:

You could use the Replace function:
select Replace(Address, "Rd.", "Road") from myTable

To make many replacements you'll want to use a VBA function and call
it from your query:
select myReplace(Address) from myTable

Then in a standard module write your function:
public function myReplace(s as string) as string
s = Replace(s, "Rd.", "Road")
s = Replace(s, "St.", "Street")
'etc.
myReplace = s
end function

-Tom.
Microsoft Access MVP

.... or he could nest the Replace calls and do it without a VBA function:

update mytable
set address=replace(replace,"Rd.","Road"),"St.","Stree t")

Of course, it's not too bad with only two calls to Replace ... adding more
would certainly make this get very messy in a hurry.

To the OP:
Is this planned to be a one-time update? If so, I would probably lean toward
making it only do a single replacement per run, and parameterize it so I
don't need to keep editing it for each replacement:

update mytable
set address = replace(address,[Enter string to be replaced],[Enter
replacement])

Then just run it every time you find something new to replace.

Tom van Stiphout wrote:

Here's a different (SQL-ish) approach that may be overkill, but might
be useful depending on the size of these tables and duration of
growth. One advantage is you can build a simple interface to allow
your client or user to add their own "renames" (Rd > Road, St >
Street, Ave > Avenue, etc) without writing new Replace() functions.

A client received laboratory data from many different labs, and they
all had slightly different names for the same chemicals (yes, some
used CAS, some didn't, I know) -- and it became a chore for me to add
new entries to the find/replace code when a new Analyte name cropped
up.

So I added a table (tblRenameAnalyte) with two fields: Original (PK),
and Rename. (Original was Primary Key because it needs to be unique.)

Then join the main data table (tblChemData, for example)'s Analyte
field to tblRenameAnalyte.Original, and, when an entry in
tblRenameAnalyte matches Analyte, it is replaced by the Rename value.
A little clunky, but I liked letting the user "teach" their own DB
what to rename.

Please email me if this seems potentially useful but too poorly
described -- it's late!

example tables:
tblChemData
Sample...
Analyte
...
...
tblRenameAnalyte
Original
Rename


UPDATE tblChemData INNER JOIN tblRenameAnalyte ON tblChemData.Analyte
= tblRenameAnalyte.Original
SET tblChemData.Analyte = tblRenameAnalyte.Rename
WHERE tblChemData.Analyte=tblRenameAnalyte.Original;




On Jul 11, 4:25*am, "Bob Barrows" <reb01... (AT) yahoo (DOT) com> wrote:

What you're describing works well if you're trying to change the entire
value of the field. Unfortunately, since Kissi needs to change "1234 Main
St." to "1234 Main Street", you'd need to have something like:

UPDATE tblChemData INNER JOIN tblRenameAnalyte
ON tblChemData.Analyte Like "*" & tblRenameAnalyte.Original & "*"
SET tblChemData.Analyte =
Replace(tblChemData.Analyte, tblRenameAnalyte.Original,
tblRenameAnalyte.Rename)

(there's no reason for the WHERE clause in your SQL statement is wrong, btw,
since it's already in the ON clause)

However, this approach is unable to handle situations like wanting to change
"1234 Main St, Apt 1A" to "1234 Main Street, Apartment 1A", should that be
an aim.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)

"Jeff" <jnorville (AT) gmail (DOT) com> wrote

Here's a different (SQL-ish) approach that may be overkill, but might
be useful depending on the size of these tables and duration of
growth. One advantage is you can build a simple interface to allow
your client or user to add their own "renames" (Rd > Road, St >
Street, Ave > Avenue, etc) without writing new Replace() functions.

A client received laboratory data from many different labs, and they
all had slightly different names for the same chemicals (yes, some
used CAS, some didn't, I know) -- and it became a chore for me to add
new entries to the find/replace code when a new Analyte name cropped
up.

So I added a table (tblRenameAnalyte) with two fields: Original (PK),
and Rename. (Original was Primary Key because it needs to be unique.)

Then join the main data table (tblChemData, for example)'s Analyte
field to tblRenameAnalyte.Original, and, when an entry in
tblRenameAnalyte matches Analyte, it is replaced by the Rename value.
A little clunky, but I liked letting the user "teach" their own DB
what to rename.

Please email me if this seems potentially useful but too poorly
described -- it's late!

example tables:
tblChemData
Sample...
Analyte
....
....
tblRenameAnalyte
Original
Rename


UPDATE tblChemData INNER JOIN tblRenameAnalyte ON tblChemData.Analyte
= tblRenameAnalyte.Original
SET tblChemData.Analyte = tblRenameAnalyte.Rename
WHERE tblChemData.Analyte=tblRenameAnalyte.Original;




On Jul 11, 4:25 am, "Bob Barrows" <reb01... (AT) yahoo (DOT) com> wrote:

All I have to add is BE CAREFUL.

All the proposed solutions can give you bad results.
1234 Richard St could end up as 1234 RichaRoad Street
2679 Lasting Hollow Avenue could end up as 2679 LaStreeting Hollow Avenuenue.

Also, the US Postal system actually wants Rd for Road and St for Street as
well as Ct for Court, etc.

At a minimum, I would use the table approach, but include spaces in the
replace function to get better results. The inclusion of spaces (as below)
should limit you to replacing only entire "words" - any part of the string
surrounded by spaces.

Trim(Replace(" " & [AddressField] & " ", " " & [OldValue] & " "," " & [New
Value] & " "))

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Douglas J. Steele wrote:

Please be careful, I got burned once using
[Addr] = Replace([Addr], "St", "Street)
[Addr] = Replace([Addr], "Rd", "Road")

I ended up with some addresses that changed from

"1234 First Ave" to "1234 FirStreet Ave"
"1234 Rexford Ave" to "1234 RexfoRoad Ave"
"1234 Rexford Street" to "1234 RexfoRoad Streetreet"


Fred





On Jul 12, 6:41*am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:

zuckermanf (AT) gmail (DOT) com wrote:
understand. Your best bet is to get software and access to the USPS
database to validate addresses. YOU provide an address and if valid, it
returns a standardized address.

I saw such some time back but don't recall exactly where. I suggest
some Google searching.

Bob