Find and Replace in Access: Beyond the Basics

Most Access users know about Find and Replace (Ctrl+H) but use only a fraction of its capabilities. And for bulk data changes, the built-in dialog is often not the right tool at all — an Update query is faster, safer, and more auditable. Here is the complete picture.

The Find and Replace Dialog

Open Find and Replace with Ctrl+H (or Home → Find → Replace). The dialog has several options that dramatically change its behavior:

Look In:

• Current Field — searches only the field where your cursor is
• Current Document — searches all fields in the current table or form

Match:

• Any Part of Field — finds the search text anywhere in the field value
• Whole Field — only matches if the entire field value equals the search text
• Start of Field — matches only if the field starts with the search text

Search: Up, Down, or All

Match Case: Case-sensitive search when checked

Search Fields As Formatted: Searches the displayed value rather than the stored value (useful for formatted numbers and dates)

Wildcard Searches

Access supports wildcard characters in the Find field:

• * — matches any number of characters: Sm* finds Smith, Smythe, Small
• ? — matches exactly one character: Sm?th finds Smith, Smyth
• # — matches any single digit: ###-#### finds phone numbers in that format
• [abc] — matches any character in the brackets: [SsT]mith finds Smith, smith, Tmith
• [!abc] — matches any character NOT in the brackets

Important: In the Find dialog, use * and ?. In query criteria, use * and ? for Access SQL (or % and _ if using ANSI SQL mode).

When to Use Update Queries Instead

The Find and Replace dialog is fine for small, one-off corrections. For anything larger, use an Update query:

Advantages of Update queries over Find and Replace:

• Runs on the entire table, not just what is visible
• Can apply complex conditions (only update records where Status = "Active")
• Can update multiple fields at once
• Runs in seconds on thousands of records
• Can be saved and rerun

Example — standardize state abbreviations to uppercase:

UPDATE Customers
SET State = UCase([State])
WHERE State <> UCase([State]);

Example — replace a specific value across all records:

UPDATE Products
SET Category = "Electronics"
WHERE Category = "Electronic";

Example — fix a common typo:

UPDATE Customers
SET City = "Philadelphia"
WHERE City = "Philidelphia";

Replacing Part of a Field Value

The Replace() function in an Update query replaces text within a field value — the equivalent of Find and Replace's "Any Part of Field" mode:

UPDATE Customers
SET Email = Replace([Email], "@oldcompany.com", "@newcompany.com");

This updates every email address that contains "@oldcompany.com", replacing just that portion.

Data Cleaning Techniques

Remove Extra Spaces

UPDATE Customers SET LastName = Trim([LastName]);
UPDATE Customers SET LastName = Replace([LastName], "  ", " ");

Standardize Phone Number Format

-- Remove all non-numeric characters first
UPDATE Contacts 
SET Phone = Replace(Replace(Replace(Replace([Phone], "-", ""), "(", ""), ")", ""), " ", "");

Fix Inconsistent Capitalization

-- Proper case is not a built-in Access function, but you can use VBA:
UPDATE Customers SET FirstName = StrConv([FirstName], vbProperCase);

StrConv with vbProperCase capitalizes the first letter of each word.

Find Records with Specific Patterns

Before replacing, use a SELECT query to audit what you will change:

SELECT CustomerID, Email FROM Customers
WHERE Email Like "*@oldcompany.com";

Always audit before updating.

Finding Null Values

Null values cannot be found with the Find dialog — it only searches for text. Use a query instead:

SELECT * FROM Customers WHERE Phone IS NULL;
SELECT * FROM Orders WHERE ShipDate IS NULL AND Status = "Shipped";

To replace nulls with a default value:

UPDATE Customers SET Phone = "Unknown" WHERE Phone IS NULL;

Bulk Replace Across Multiple Tables

If you need to replace a value in multiple tables (e.g., a customer ID changed), run separate Update queries for each table, or write a VBA procedure that loops through the affected tables:

Sub UpdateCustomerID(oldID As Long, newID As Long)
    Dim db As Database
    Set db = CurrentDb
    
    db.Execute "UPDATE Orders SET CustomerID = " & newID & " WHERE CustomerID = " & oldID
    db.Execute "UPDATE Invoices SET CustomerID = " & newID & " WHERE CustomerID = " & oldID
    db.Execute "UPDATE Contacts SET CustomerID = " & newID & " WHERE CustomerID = " & oldID
    
    MsgBox "CustomerID updated in all tables."
End Sub

Conclusion

The Find and Replace dialog is useful for quick, small-scale corrections. For anything involving more than a handful of records, Update queries with the Replace() function are faster, more reliable, and easier to audit. Build the habit of using queries for bulk data changes and you will avoid the mistakes that come from manual, record-by-record editing.