Access 2007 Replace operation

5 pts.
Tags:
Access 2007
Microsoft Access
In Access 2007, can a replace operation be performed using wildcard to replace a variable string with a known string.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following