Updating A Text String In A Text Field With An Update Query

[Sheldon]

If I have the following string in a text field of a table:
"This is a test"
can I create an update query that will change, say, the string "is" to
"xyz" in the two locations where it occurs or would this require VBA?
Thanks,
Sheldon Potolsky

[Allen Browne]

Use an Update query.

Something like this:
UPDATE Table1
SET [F1] = Replace([F1], "is", "xyz")
WHERE [F1] Like "*is*";

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sheldon" <SH********@aol.comwrote in message
news:61**********************************@y38g2000 hsy.googlegroups.com...

If I have the following string in a text field of a table:
"This is a test"
can I create an update query that will change, say, the string "is" to
"xyz" in the two locations where it occurs or would this require VBA?
Thanks,
Sheldon Potolsky

[Sheldon]

Thank you Allen; that worked great.

As a followup, if I also wanted to change all occurrences of "t" or
"T" to "123" in the string "This is a test", would I need an
additional query? In my real world example, I'm trying to remove
certain strings such as "<br/>" and "&nbsp;" whenever they occur in a
closing description of a problem.

Thank you, Sheldon

>
Something like this:
* * UPDATE Table1
* * SET [F1] = Replace([F1], "is", "xyz")
* * WHERE [F1] Like "*is*";

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sheldon" <[e-mail] in message

news:61**********************************@y38g2000 hsy.googlegroups.com...

If I have the following string in a text field of a table:
"This is a test"
can I create an update query that will change, say, the string "is" to
"xyz" in the two locations where it occurs or would this require VBA?
Thanks,
Sheldon Potolsky- Hide quoted text -

- Show quoted text -

[Allen Browne]

Yes: execute an Update query for each replacement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sheldon" <SH********@aol.comwrote in message
news:95**********************************@i76g2000 hsf.googlegroups.com...
Thank you Allen; that worked great.

As a followup, if I also wanted to change all occurrences of "t" or
"T" to "123" in the string "This is a test", would I need an
additional query? In my real world example, I'm trying to remove
certain strings such as "<br/>" and "&nbsp;" whenever they occur in a
closing description of a problem.

Thank you, Sheldon

>
Something like this:
UPDATE Table1
SET [F1] = Replace([F1], "is", "xyz")
WHERE [F1] Like "*is*";

"Sheldon" <[e-mail] in message

news:61**********************************@y38g2000 hsy.googlegroups.com...

If I have the following string in a text field of a table:
"This is a test"
can I create an update query that will change, say, the string "is" to
"xyz" in the two locations where it occurs or would this require VBA?
Thanks,
Sheldon Potolsky- Hide quoted text -

- Show quoted text -