Need to replace text in a description field of Access 2007

J.R. Sitman
J.R. Sitman used Ask the Experts™
on
In the description field of our database I need to find and replace I'm with I am.  When I try this using the find and replace it doesn't find anything.  So I tried making an update query with I am as the update to and I'm as the criteria.  It finds o records.  There are plenty of records with "I'm" so how do I change it?

The reason I need this ASAP is because the "I'm" code messes up our uploads of the database to PetFinder.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Try using this in your Update query:

Replace([YourFieldName],"I" & Chr(39) & "m" , "I am")

Chr(39) is the apostrophe.

mc
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Update yourtable
Set Decription = replace(description, "I'm", "I am")


Make a backup before trying this.
J.R. SitmanIT Director

Author

Commented:
see attachment.   what is wrong?
query.jpg
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Database Architect / Application Developer
Top Expert 2007
Commented:
Change your Criteria to:

InStr(1,[Description], "I" & Chr(39) & "m") >0

mx
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
The criteria is only allowing descriptions that are exactly  I'm.

Did you try what i posted?   It should work for descriptions that *contain*  I'm.  Its the criteria that is messing you up.
J.R. SitmanIT Director

Author

Commented:
worked.

Thanks
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Ignore my last post ...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
No ... my post could not have worked ... any place a match was found, you would only end up with
I am ...

mx
J.R. SitmanIT Director

Author

Commented:
I'm confused.  It stated it found 524 records, so I updated it.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
This will work:

UPDATE TableA SET TableA.Description = Replace([Description],"I" & Chr(39) & "m","I m")
WHERE (((TableA.Description) Is Not Null));
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Mx...it looks good. :-)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"so I updated it. "
Better look at your data .... :-(

Sorry ...
J.R. SitmanIT Director

Author

Commented:
so what do I search for that you think got changed?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"Mx...it looks good. :-) "

?
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Joe... the replace statement you posted should have done the changes right.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
You mean @ ID: 38719444 ?
If so ... then yes.  But not the 'Criteria' post ... cuz it would look 'gone!'

mx
J.R. SitmanIT Director

Author

Commented:
With this query it's finding 2048 which is more relaistic.  I'm still wondering what the other query updated?

UPDATE TableA SET TableA.Description = Replace([Description],"I" & Chr(39) & "m","I m")
WHERE (((TableA.Description) Is Not Null));
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"what the other query ..."

Which other query ?

mx
J.R. SitmanIT Director

Author

Commented:
this

Change your Criteria to:

InStr(1,[Description], "I" & Chr(39) & "m") >0

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
That ... would hose your data.  Any place in Description where I'm was found ... the entire Description would be replaces with just I am - based on what you have in the Update To cell.

However ...

IF ... you used my very first post (ie, the first post in the thread) ... then you are OK.
J.R. SitmanIT Director

Author

Commented:
Well I do see several descriptions with just I am, however, many more with the proper description.  So not really sure exactly what happened.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Well ... part good and part bad.

The correct version is @ http:#a38719465 

mx
J.R. SitmanIT Director

Author

Commented:
how would I search in a query for just the '
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Criteria:

Instr(1, [Description], Chr(39)) > 0

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial