Why 'You can't replace the current value of the field with the replacement text'

[Pater Maximus]

I have a 104,000 entry Access 2000 database. I want to find and replace all
instances of one letter in a text field with another letter. The field is
not indexed not a look up field and I allow zero length. There are no
validation rules.

I get the above message when I do a find and replace all in the table view

I even get it if I try to replace a letter by itself.

Could it be that I am trying to replace too many instances. If so, how can I
set the limit?

 

[Wayne Morgan]

Well, since we don't know how you were going about it, it is hard to guess what the
problem may be. This query will replace the all instances of the letter x in Field1 of
Table1 with the letter z

UPDATE Table1 SET Table1.Field1 = Replace([Field1],"x","z");

 

[John Nurick]

Hi Wayne,

This only works in some installations of Access 2000. Normally you can't
use Replace() in a query in Access 2000 and have to create a wrapper UDF
in a module, something like

Public Function MyReplace(TheField As Variant, _
Target As String, Replacement As String) _
As Variant

If IsNull(String1) Then
MyReplace = Null
Exit Function
End If
MyReplace = Replace(CStr(TheField), _
Target, Replacement)
End Function

and then use this in the query.

Replace can be used freely in queries from Access 2002 on; AIUI it only
works in Access 2000 if some apparently unrelated service pack has been
installed (but I can't remember which and I never use Access 2000
myself).

Well, since we don't know how you were going about it, it is hard to guess what the
problem may be. This query will replace the all instances of the letter x in Field1 of
Table1 with the letter z

UPDATE Table1 SET Table1.Field1 = Replace([Field1],"x","z");

 

[Wayne Morgan]

Thanks John,

I had tried it on XP and knew that 2000 had the function, but wasn't aware of the "bug".