Find and Replace Macro Example needed

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I need to update several records in a specific field. Is there a way to do
a Find and Replace Macro or Module?

I need an example to get started.

I would like to search a [Descr} field for "AK." and replace it with "AK",
then "MO." and replace with "MO"

Thanks in advance
 
The most efficient way to do this kind of bulk update is usually with an
update query ...

CurrentDb.Execute "UPDATE NameOfTable SET NameOfField = 'AK' WHERE
NameOfField = 'AK.'"
CurrentDb.Execute "UPDATE NameOfTable SET NameOfField = 'MO' WHERE
NameOfField = 'MO.'"

Or, if you want to to strip the "." from all records that have it as the
last character ...

CurrentDb.Execute "UPDATE NameOfTable SET NameOfField = Left$([NameOfField,
Len(NameOfField) - 1) WHERE Right$(NameOfField, 1) = '.'"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
try to use Update query:
update MyTable Set [Descr]='AK' Where [Descr] = 'AK.'

you can also use replace function there
 
Do I just keep repeating the code in the same query for each item I need to
fix, if so, how do I separate the statements, with a semi-colon?

Thanks for the info!

Dave
 
No. You can do things like that in SQL Server, but Jet queries must consist
of a single SQL statement. If the changes don't fit into some recognizable
pattern as in the example I posted using Left$() and Right$(), then you will
have to run multiple update queries.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Dave said:
Do I just keep repeating the code in the same query for each item I need to
fix, if so, how do I separate the statements, with a semi-colon?

Thanks for the info!

Dave
Dave said:
I need to update several records in a specific field. Is there a way to do
a Find and Replace Macro or Module?

I need an example to get started.

I would like to search a [Descr} field for "AK." and replace it with "AK",
then "MO." and replace with "MO"

Thanks in advance
Click to expand...
Click to expand...
 
for each search you have to run a new SQL

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Dave said:
Do I just keep repeating the code in the same query for each item I need
to fix, if so, how do I separate the statements, with a semi-colon?

Thanks for the info!

Dave
Dave said:
I need to update several records in a specific field. Is there a way to
do a Find and Replace Macro or Module?

I need an example to get started.

I would like to search a [Descr} field for "AK." and replace it with
"AK", then "MO." and replace with "MO"

Thanks in advance
Click to expand...
Click to expand...
 
Back
Top