Well, after replying to your question & then pondering some … I noticed Access 2007 does have Find/Replace option. After opening the program & the table and clicking the field you want to change… click on the Home Tab — Find (Binocular symbol) will be on the right hand side. Click on it — on the Find and Replace screen — Select the Field name in “Look in” box, & then on “Match” box you can select 1) Whole Field 2) Start of Field 3) Any part of Field. Select “Any part of field” & then corresponding Replace With field values.
This is much easier than my previous convoluted process.
Access 2007 made radical changes compared to previous versions – some of the functions used to work — now, I cannot make them work. Like; concatenate (works in Excel 2007). May be I need to study a bit hard!
For simple change/replace – if the pattern is at the start (or end of field) you can do with this rough idea. For complex (any position in the field) you need to write a VB code examining the pattern and noting the position. Here is a simple case – suppose you want to change field starting with or ending with “ABCD with “WXYZ”. Set up a query to display the field & the length of the field. Now you can change that query to update query. Enter as follows on the query design form:
Update to: “WXYZ” & Mid([PartNo],5,[Leng] – 4)
Criteria : like “ABCD*” Note: Leng is the length of the field LEN(PartNo)
The equivalent SQL will be:
UPDATE qryLength SET qryLength.PartNo = “WXYZ” & Mid([PartNo],5,[Leng]-4)
WHERE (((qryLength.PartNo) Like “abcd*”));
If the pattern is at the end use:
UPDATE qryLength SET qryLength.PartNo = Mid([PartNo],1,[Leng]-4) & “WXYZ”
WHERE (((qryLength.PartNo) Like “*abcd”));
Hope this answers your question. Good luck.