will Find *abc* and replace with *xyz* work? (using an update query)

[Mel]

can I use an Update Query to find...

*abc* and replace it with *xyz*

where the prefix and suffix may not be the same number of characters?

 

[David W. Fenton]

You don't need to include the wildcard characters for this, just
call the Replace function:

UPDATE [MyTable]
SET [MyField] = REPLACE([MyField],"abc", "xyz");

All instances of "abc" anywhere within the value of the field will
be replaced with "xyz".

It's highly recommended to make a backup of the table before running
such an update...

 

[Dude8383]

I'm a bit new to Access and I was wondering if it would be possible to
include a button in a data adapter that will do this function.

In a nutshell I need to do a find and replace just like the author
asked however I only need to do it occasionally. I don't want the find
and replace to run each time I open up the database, hence the reason
why I'm asking if its possible to create a separate button.

Check out the links to images to see what I mean.

http://lh3.ggpht.com/_wyi3Rqkw7No/Sp_u-Wd-8QI/AAAAAAAAA1s/IGTCrLCW5Dg/s800/home.JPG

This is the file that I open. The first thing I do is "import from
mas90" <--- this is our accounting software

Next thing I do is index the imported tables and then proceed to find
and replace.

http://lh3.ggpht.com/_wyi3Rqkw7No/Sp_u-1fo5II/AAAAAAAAA10/sDkhovA0BnI/s800/table.JPG

The highlighted column area is where I find & replace.

http://lh5.ggpht.com/_wyi3Rqkw7No/Sp_u-Tnky_I/AAAAAAAAA1o/otsYl8df3RI/s800/design_view.JPG

The design_view.jpg is what I see when I open the design mode. I have
no idea where the code goes.

http://lh3.ggpht.com/_wyi3Rqkw7No/Sp_u-rw14BI/AAAAAAAAA1w/SU0hdrreDHs/s800/modules.JPG

However, if I go to modules I see code. The form adapter appears to be
the code that pulls the info from the acc software. The module code
appears to be the code that indexes.

http://lh6.ggpht.com/_wyi3Rqkw7No/Sp_u9x6ni_I/AAAAAAAAA1k/UhwuMyc72LY/s800/code.JPG

Here's the catch, I only edit this column for one specific customer,
so this would essentially have to be a similar button like "import
from mas90" and "index...."

I hope that this isn't too confusing! I'm going to be reading up on
how this stuff works just curious if anyone has had similar issues.

Thank you!

You don't need to include the wildcard characters for this, just
call theReplacefunction:

UPDATE [MyTable]
SET [MyField] =REPLACE([MyField],"abc", "xyz");

All instances of "abc" anywhere within the value of the field will
be replaced with "xyz".

It's highly recommended to make a backup of the table before running
such an update...

 

[KARL DEWEY]

Not sure of what you want because you did not take advantage of the big white
space to ask your question.

But replace has this syntax --
Replace([YourField], "Old_Text", "New_Text")

You can nest replace if needed.