Find and Replace Macro Example needed

[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

 

[Brendan Reynolds]

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.

 

[Alex Dybenko]

try to use Update query:
update MyTable Set [Descr]='AK' Where [Descr] = 'AK.'

you can also use replace function there

 

[Dave]

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

 

[Brendan Reynolds]

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.

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

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

 

[Alex Dybenko]

for each search you have to run a new SQL

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

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

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

 

[Dave]

Thanks!