Need to replace text in a description field of Access 2007

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.
J.R. SitmanIT DirectorAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Change your Criteria to:

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

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try using this in your Update query:

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

Chr(39) is the apostrophe.

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


Make a backup before trying this.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
J.R. SitmanIT DirectorAuthor Commented:
see attachment.   what is wrong?
query.jpg
0
 
mbizupCommented:
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.
0
 
J.R. SitmanIT DirectorAuthor Commented:
worked.

Thanks
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Ignore my last post ...
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
No ... my post could not have worked ... any place a match was found, you would only end up with
I am ...

mx
0
 
J.R. SitmanIT DirectorAuthor Commented:
I'm confused.  It stated it found 524 records, so I updated it.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This will work:

UPDATE TableA SET TableA.Description = Replace([Description],"I" & Chr(39) & "m","I m")
WHERE (((TableA.Description) Is Not Null));
0
 
mbizupCommented:
Mx...it looks good. :-)
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"so I updated it. "
Better look at your data .... :-(

Sorry ...
0
 
J.R. SitmanIT DirectorAuthor Commented:
so what do I search for that you think got changed?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"Mx...it looks good. :-) "

?
0
 
mbizupCommented:
Joe... the replace statement you posted should have done the changes right.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You mean @ ID: 38719444 ?
If so ... then yes.  But not the 'Criteria' post ... cuz it would look 'gone!'

mx
0
 
J.R. SitmanIT DirectorAuthor 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));
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"what the other query ..."

Which other query ?

mx
0
 
J.R. SitmanIT DirectorAuthor Commented:
this

Change your Criteria to:

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

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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.
0
 
J.R. SitmanIT DirectorAuthor 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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well ... part good and part bad.

The correct version is @ http:#a38719465 

mx
0
 
J.R. SitmanIT DirectorAuthor Commented:
how would I search in a query for just the '
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Criteria:

Instr(1, [Description], Chr(39)) > 0
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.