Update part of a field

[Roger Bell]

Is there a way you can use an Update query or other procedure to update part
of a field: Eg: Smith St, Smith Cam Rd, where just the St or Rd would be
updated to Street or Road?
Thanks for any help ( the field is called 'Street Name')

 

[Keith Wilby]

Is there a way you can use an Update query or other procedure to update
part
of a field: Eg: Smith St, Smith Cam Rd, where just the St or Rd would be
updated to Street or Road?
Thanks for any help ( the field is called 'Street Name')

Just use the search and replace function as you would in a Word document.

Keith.
www.keithwilby.com

 

[John Spencer]

Carefully.

If you are using Access 2000 and later (and have all the service packs for
2000) you can use the replace function. However, that can be dangerous.
Since you don't want to change
St Barnabas St to Street Barnabas Street
or 1st St to 1Street Street
or Avalon Place to Avenuealon Place

If you make the assumption that what you want to replace is the last "word"
in the field then you could use an update like the following to change
1st St to 1st Street
but leave
North 1st
unchanged

An update query would look like the following
UPDATE YourTable
SET [Street Name]= Left([Street Name] ,Len([Street Name])-2) & "Street"
WHERE [Street Name] Like "* St"

In the query grid
Field: Street Name
Criteria: Left([Street Name] ,Len([Street Name])-2) & "Street"
Update to: Like "* St"

You would need to adjust that for Ave, Rd, etc.

IF you have only a few records to update, Find and Replace and the human
brain will be the most accurate.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..