Thanks...I tend to forget that there's an SQL version of the Select Case
statement.

"John Vinson" wrote in message
news
On 5 Mar 2006 12:44:55 -0800, "JR" wrote:

I need to update all values in a database column to new values. The
way it works is basically:

if = abc replace with 123
if = bcd replace with 234
if = cde replace with 345
if = def replace with 456

Now the values used above are just for example it's not really pattern
orientated. I can create four update queries for this, however was
hoping there was a way to do this in one shot. Is there a single
update query that could update the source values with new values based
on rules like the above?

Amy's suggestion is certainly one way if you don't have *too* many
values (nested IIF's will eventually be too complicated to handle).

Back up your database first, just in case of problems - update queries
are NOT reversible!

The next step up is to use the Switch() function: it takes arguments
in pairs, and if the first member of the pair is True it will return
the second argument and quit. That is, you could run an Update query
updating the field to

Switch([field] = "abc", 123,
[field] = "bcd", 234,
[field] = "cde", 345,
[field] = "def", 456,
... etcetera,
True, [Field])

The last value ensures that the field will be updated to its current
value (left unchanged in other words) if it doesn't match any of the
choices.

The next step up from *there* is to create a translation table with
two fields, the old and new values. Make the old value the Primary Key
of this table. Then create an Update query joining your current table
to the update table, change it to an Update query, and update to

[TranslationTable].[NewValue]

using your actual table and fieldname. The square brackets are
required (otherwise it will update all records to the text string
"TranslationTable.NewValue"!!!)

John W. Vinson[MVP]