Find/Replace on different formats

[Susan]

I need to automatically run Find & Replace on a table
from a macro to check for spaces in any field and replace
them all with a "+". Is there a way to do this from a
macro? I found the RunCommand -- Replace that opens the
Find & Replace window on the table, but I still have to
type in the criteria each time. This is Access 2000.

Thanks for your help!
Susan

 

[Steve Schapel]

Susan,

You need to use an Update Query for this. Then you can use a macro
with an OpenQuery action to run the update.

The details depend on your version of Access. If you are using Access
2002 or Access 2003, you can simply use the Replace function. Update
YourField to...
Replace([YourField]," ","+")
This also applies to Access 2000 under some circumstances (depending
on the Service Release installed). Try it and see
If this doesn't work for you in Access 2000, or if you are using an
earlier version of Access, post back, as you will have to create a
user-defined function in a module.

- Steve Schapel, Microsoft Access MVP

 

[Susan]

Thanks Steve!
That worked perfectly in the query!

-----Original Message-----
Susan,

You need to use an Update Query for this. Then you can use a macro
with an OpenQuery action to run the update.

The details depend on your version of Access. If you are using Access
2002 or Access 2003, you can simply use the Replace function. Update
YourField to...
Replace([YourField]," ","+")
This also applies to Access 2000 under some circumstances (depending
on the Service Release installed). Try it and see
If this doesn't work for you in Access 2000, or if you are using an
earlier version of Access, post back, as you will have to create a
user-defined function in a module.

- Steve Schapel, Microsoft Access MVP