Modify value already in a table from a comb

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hello to everybody,
in a combox I have a description like: "hairdresser".
I need to change this value to "coiffeur-beautician" not only in the combo,
but automatically (if possible) also in the table where a lot of records
contains the value "hairdresser".

Many thanks and regards
John
 
Do the existing records contain simply the word hairdresser, or does the
word hairdresser appear within a phrase?

If the former, a simply Update query is what you need:

UPDATE MyTable
SET MyField = "coiffeur-beautician"
WHERE MyField = "hairdresser"

In the latter case, assuming you're using Access 2000 or newer, you should
be able to use:

UPDATE MyTable
SET MyField = Replace(MyField, "hairdresser", "coiffeur-beautician")
WHERE MyField LIKE "*hairdresser*"

Note: if you are using Access 2000, make sure you've installed all of the
service packs. The Replace function didn't work in SQL queries in the first
release of Access 2000. If installing the service packs doesn't solve that
problem, you can always create your own "wrapper function" that uses
Replace:

Function MyReplace(TextIn As String, _
ChangeFrom As String, _
ChangeTo As String _
) As String

MyReplace = Replace(TextIn, ChangeFrom, ChangeTo)

End Function

then use that wrapper in place of Replace:

UPDATE MyTable
SET MyField = MyReplace(MyField, "hairdresser", "coiffeur-beautician")
WHERE MyField LIKE "*hairdresser*"
 
Many thanks.
It works!. The word hairdresser doesn't appear within a phrase. It is
alone.

Kind regards
John
 
Back
Top