changing "road" to "rd" for a number of address records at once

[Liz McCracken]

Take Like off the Update to row and just have the RD.
Leave the criteria as is.
Good Luck!
Liz McCracken, MOS

 

[Armen Stein]

Take Like off the Update to row and just have the RD.
Leave the criteria as is.
Good Luck!
Liz McCracken, MOS

This will change the entire contents of the field to "RD", replacing the
address that is there. That's probably not what you want.

Instead, you can use Replace in the Update To:

replace([YourAddressField],"ROAD","RD")

 

[Liz McCracken]

Armen,
Sorry, but I disagree. As long as the criteria is marked
in the criteria row - as "Like ROAD" the query will only
update anything that is ROAD to RD.

-----Original Message-----

Take Like off the Update to row and just have the RD.
Leave the criteria as is.
Good Luck!
Liz McCracken, MOS

This will change the entire contents of the field to "RD", replacing the
address that is there. That's probably not what you want.

Instead, you can use Replace in the Update To:

replace([YourAddressField],"ROAD","RD")

--
Armen Stein
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
.

 

[Cheryl Fischer]

Hello Liz,

Tested this out in a query using the following SQL in Access 2002:

UPDATE [TestAddressTable] SET [TestAddressTable].Address1 = "Rd"
WHERE ((([TestAddressTable].Address1) Like "*Road"))

The above query over-wrote the full address which included Street Number,
Street Name ending in *Road and replaced it with "Rd" only.

Did I miss something? It would be very nice if this did work, particularly
in versions of Access where the Replace() function is not available.
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Armen,
Sorry, but I disagree. As long as the criteria is marked
in the criteria row - as "Like ROAD" the query will only
update anything that is ROAD to RD.

-----Original Message-----

Take Like off the Update to row and just have the RD.
Leave the criteria as is.
Good Luck!
Liz McCracken, MOS
-----Original Message-----
trying to clean up our client database, and want to
change "street", "road", etc. to the abbreviation without
having to go to each record and manually change it.

i think what i want to do is an update query, but i'm not
understanding the syntax. i start a query to look for "*
ROAD" and get all the records containing "ROAD". when i
select "update query" the "critera" field displays "Like
*ROAD" so i copy that to the "update to" field, change
ROAD to RD and get a syntax error.

any ideas?

thank you
.

This will change the entire contents of the field to "RD", replacing the
address that is there. That's probably not what you want.

Instead, you can use Replace in the Update To:

replace([YourAddressField],"ROAD","RD")

--
Armen Stein
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
.

 

[Dale Fye]

Liz, I agree with Armen, using Replace is the best bet.

Based on your previous comment, I think the SQL would look like

Update myTable
SET [addStreet] = 'RD'
WHERE [addStreet] Like '*ROAD'

but this would change the entire contents of [addStreet] to 'RD' for
those records that end in 'Road'. Additionally, this method would not
modify those records where 'Road' is not the final 4 characters of the
address.

--
HTH

Dale Fye


Armen,
Sorry, but I disagree. As long as the criteria is marked
in the criteria row - as "Like ROAD" the query will only
update anything that is ROAD to RD.

-----Original Message-----

Take Like off the Update to row and just have the RD.
Leave the criteria as is.
Good Luck!
Liz McCracken, MOS

This will change the entire contents of the field to "RD", replacing the
address that is there. That's probably not what you want.

Instead, you can use Replace in the Update To:

replace([YourAddressField],"ROAD","RD")

--
Armen Stein
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
.

 

[merrilee]

i tried liz's suggestion and it did change the entire
address to just "rd" (thank you anyway, though).

i copied armen's "replace" example (thank you) and
substituted my field name, also provided the asterisk, but
when i try that i get "undefined function replace in
expression".

-----Original Message-----
Criteria should actually say "Like *ROAD"
Sorry...left the * out on my response.

-----Original Message-----
Armen,
Sorry, but I disagree. As long as the criteria is marked
in the criteria row - as "Like ROAD" the query will only
update anything that is ROAD to RD.

-----Original Message-----
Take Like off the Update to row and just have the RD.
Leave the criteria as is.
Good Luck!
Liz McCracken, MOS
-----Original Message-----
trying to clean up our client database, and want to
change "street", "road", etc. to the abbreviation without
having to go to each record and manually change it.

i think what i want to do is an update query, but

i'm
not

understanding the syntax. i start a query to look for "*
ROAD" and get all the records containing "ROAD".

when

i
select "update query" the "critera" field displays "Like
*ROAD" so i copy that to the "update to" field, change
ROAD to RD and get a syntax error.

any ideas?

thank you
.



This will change the entire contents of the field to "RD", replacing the
address that is there. That's probably not what you want.

Instead, you can use Replace in the Update To:

replace([YourAddressField],"ROAD","RD")

--
Armen Stein
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
.

.

.

 

[merrilee]

THANK YOU!!!

-----Original Message-----
Merrilee,

The Replace() function is only directly available in Access 2002. If
you'll use the following in your Update To line, I believe you'll get the
results you want:

Left([Address1],InStr([Address1],"Road")-1) & "Rd"

The Criteria row should continue to read: Like "*road". And, as was
pointed out earlier, this will only work where the last word in the field is
"road".

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

i tried liz's suggestion and it did change the entire
address to just "rd" (thank you anyway, though).

i copied armen's "replace" example (thank you) and
substituted my field name, also provided the asterisk, but
when i try that i get "undefined function replace in
expression".

-----Original Message-----
Criteria should actually say "Like *ROAD"
Sorry...left the * out on my response.
-----Original Message-----
Armen,
Sorry, but I disagree. As long as the criteria is marked
in the criteria row - as "Like ROAD" the query will only
update anything that is ROAD to RD.

-----Original Message-----
Take Like off the Update to row and just have the RD.
Leave the criteria as is.
Good Luck!
Liz McCracken, MOS
-----Original Message-----
trying to clean up our client database, and want to
change "street", "road", etc. to the abbreviation
without
having to go to each record and manually change it.

i think what i want to do is an update query, but i'm
not
understanding the syntax. i start a query to look
for "*
ROAD" and get all the records containing "ROAD".
when
i
select "update query" the "critera" field
displays "Like
*ROAD" so i copy that to the "update to" field,
change
ROAD to RD and get a syntax error.

any ideas?

thank you
.



This will change the entire contents of the field
to "RD", replacing the
address that is there. That's probably not what you
want.

Instead, you can use Replace in the Update To:

replace([YourAddressField],"ROAD","RD")

--
Armen Stein
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com
.

.

.

.