How do I use an update query to replace "Apt" with "#" ?

[Debbie?]

I have 6000 records for names and addresses. The address fields sometimes
contain "Apt". The application that I am using this file for requires that
Apartments be designated by the # symbol. Every time I try to update, it
wipes out the entire address, and leaves the #.

 

[John W. Vinson]

I have 6000 records for names and addresses. The address fields sometimes
contain "Apt". The application that I am using this file for requires that
Apartments be designated by the # symbol. Every time I try to update, it
wipes out the entire address, and leaves the #.

Let's say the fieldname is Address. Run an Update query updating Address to

Replace("Address", " Apt ", " # ")

I'm padding the apt with blanks to prevent changing "Clapton St" to "Cl#on
St". You may need to tweak this to fit your data.

The key is that you're using the Replace function to generate a modified text
string based on the existing address.

 

[Debbie?]

Thanks for trying, John.
The result was the entire record was "Address" and nothing else. For all
6000 records, not just the ones that had Apt in them. Can you help?

 

[John Spencer]

John had a small error in his expression

Replace([Address]," APT "," # ")

Not that the field name is enclosed in square brackets - not quotes.

As always, back up your data first - just in case the result is not what
you want.

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

 

[John W. Vinson]

John had a small error in his expression

Replace([Address]," APT "," # ")

Not that the field name is enclosed in square brackets - not quotes.

<BLUSH>

Thanks John, and my sincere apologies, Debbie.