Find and replace error

[zoone]

Hi

I've got an Access-table with about 50000 records and when i try to search & replace some of the data in a column, I keep getting this error after around 5000 replacements:

"You can't replace the current value of the field with the replacement text. Resolve any errors before making further replacements"

I've tried to change the options like "start of field" or "any part of field", but no luck.. It seems to happen no matter what table I want to run a S&R in, just as long as it has more than 5000 records...

Help is much appreciated

PS. My Access version is 2003 (11.5614.5703)

 

[lupins46]

I can confirm the problem exists in A2003. I just run a find & replace on a table of 70000 records and I get the same error after around 9500 records when I am in a boolean field.

The number of records which it 'accepts' appears to be variable and I haven't been able to get any general rule yet.

Having done some more investigation, the problem seems to occur if you display 0 and -1 values and does not occur if you display True/False or use a checkbox as the display control.

Does any of this tie in with your problem.

 

[zoone]

I dont really know what you mean about "0" and "-1" (they call me teh coder noob but I've just got a column with some file paths in (like. c:\database\pic\size2\dungeon002.jpg) and I want to replace some of the text with null, so the result is \dungeon002.jpg instead of the full path. If I S&R for the part "c:\database\pic\size2" I can go through something like 4900-5000 results before the error comes up. If I only do a S&R for "\" it runs through approx. 15000 before coming up with the error.... Sure is strange

 

[lupins46]

You could create an update query to achieve the same thing.
It will need to be quite carefully defined though.
Using your example:

Update mytable set myfield = mid(myfield,22)
where myfield like "c:\database\pic\size2"&"*"

Make sure you have a backup of your table before testing this.