Making a wild hash of things

[Stapes]

Hi

I spotted a number of fields with # in them in our customers database,
so I thought I would remove them by doing a global edit. I tried
finding # and replacing with nothing. Then click replace all. I got an
error message - but that was not all. It was the postcode field. Next
day, all the postcodes had changed. The letters were there, but loads
of the numbers had gone. PL24 2SB had become PL SB. Lucky we had a
backup.

Today, I tried a similar thing. We had loads of records with ??? in a
particular field, which I tried replacing with nothing. This picked up
& replaced loads of other records with a three-letter code in the
appropriate field. Like "BEN" or "JET".

I will know better in future.

Stapes

 

[Guest]

Yep. Both the # and ? are wildcards. The # matches any numerical character
while a ? matches any character. Just be happy that you didn't try * as it
matches everything.

As a rule, don't use find and replace to change data. You are much better
off using an using an update query as (1) it's faster and (2) safer.

UPDATE A
SET A.INDEX_OWNER = Replace([INDEX_OWNER],"#","")
WHERE A.INDEX_OWNER Like "*[#]*";

By putting the # in brackets, it looks for the literal character instead of
a wildcard. The Replace function will then find all the #'s and turn them
into an empty string. In fact you could probably do away with the WHERE
clause altogether; however, by having the WHERE clause you could first run it
as a select query or go to datasheet view to make sure that you are only
going to update the correct records.