Link to home
Start Free TrialLog in
Avatar of J.R. Sitman
J.R. SitmanFlag for United States of America

asked on

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.
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Try using this in your Update query:

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

Chr(39) is the apostrophe.

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


Make a backup before trying this.
Avatar of J.R. Sitman

ASKER

see attachment.   what is wrong?
query.jpg
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
worked.

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

mx
I'm confused.  It stated it found 524 records, so I updated it.
This will work:

UPDATE TableA SET TableA.Description = Replace([Description],"I" & Chr(39) & "m","I m")
WHERE (((TableA.Description) Is Not Null));
Mx...it looks good. :-)
"so I updated it. "
Better look at your data .... :-(

Sorry ...
so what do I search for that you think got changed?
Joe... the replace statement you posted should have done the changes right.
You mean @ ID: 38719444 ?
If so ... then yes.  But not the 'Criteria' post ... cuz it would look 'gone!'

mx
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));
"what the other query ..."

Which other query ?

mx
this

Change your Criteria to:

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

mx
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.
Well I do see several descriptions with just I am, however, many more with the proper description.  So not really sure exactly what happened.
Well ... part good and part bad.

The correct version is @ http:#a38719465 

mx
how would I search in a query for just the '
Criteria:

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