I have a relatively simple, single table database containing 300 records that needs some updating.
There are ten text fields in each record. Any one of those fields may contain the string that I want to change. I staggered the criteria for the update query so Access combines them with the OR operator. The sql view is as follows:-
UPDATE Customers SET Customers.firstname1 = "JOE", Customers.firstname2 = "JOE", Customers.firstname3 = "JOE", Customers.firstname4 = "JOE", Customers.firstname5 = "JOE", Customers.firstname6 = "JOE", Customers.firstname7 = "JOE", Customers.firstname8 = "JOE", Customers.firstname9 = "JOE", Customers.firstname10 = "JOE"
WHERE (((Customers.firstname1)="FRED") OR ((Customers.firstname2)="FRED") OR ((Customers.firstname3)="FRED") OR ((Customers.firstname4)="FRED") OR ((Customers.firstname5)="FRED") OR ((Customers.firstname6)="FRED") OR ((Customers.firstname7)="FRED") OR ((Customers.firstname8)="FRED") OR ((Customers.firstname9)="FRED") OR ((Customers.firstname10)="FRED"));
I'm concerned that if executed, this statement would fill every firstname field with "JOE" in any record if it finds only one instance of "FRED".
Ideally I only want the query to update fields that contain "FRED" and not fill every FirstName field with "JOE" regardless.
don't execute the query! It would enter indeed everywhere "JOE" and you don't want that to happen!
Simple solution : open the table itself and use the command <Edit><Replace> in the menu bar. Make sure the 'Look In' value is set to the table, so every instance of "FRED" will be replaced by "JOE" and only in those fields where it appears.