For small tables, the Find-and-Replace functionality in Access does provide a quick and easy way to alter data and save time.
But for large tables, the Find-and-Replace functionality essentially crashes and burns, able to process only a few thousand records at a time.
If you do have large tables that choke the Find-and-Replace functionality in Access, one alternative is the REPLACE function.
The REPLACE function replaces a specified text string with a different string.
The basic syntax for the REPLACE function mandates three required arguments:
‘Expression to Search’: the full string you are evaluating. In a query environment, you can use the name of a field to specify that you are evaluating all the row values of that field.
‘What to Find’: the substring you need to find and replace.
‘What to Replace it With’: the substring used as the replacement.
Replace(“Pear”, “P”, “B”) would return “Bear”
Replace(“Now Here”, ” H”, “h”) would return “Nowhere”
Replace(“Microsoft Access”, “Microsoft “, “”) would return “Access”
You would normally implement REPLACE in an Update query.
Here is an example of an Update query where the REPLACE function is used to find all instances of “blvd’ in the Address field, replacing them with “Boulevard”.
Obviously, the neato thing about REPLACE is that you can alter specific parts of a field entry as opposed to overwriting the entire entry.
