Update Query-- in reference to Find & Replace

John, thanks for the reply. The column name is "PAT RELATION TO INSD" The
fields are populated with a " X" or a " X" or a "" what I need to
do is replace the first with "Self" the second with "Spouse" and the third
with "Child". Will an update query work for this? and how? Thanks for all
your help.

On Mon, 13 Dec 2004 14:05:05 -0800, "Chris"
wrote:

John, thanks for the reply. The column name is "PAT RELATION TO INSD" The
fields are populated with a " X" or a " X" or a "" what I need to
do is replace the first with "Self" the second with "Spouse" and the third
with "Child". Will an update query work for this? and how? Thanks for all
your help.

You'll need to use either three update queries in succession, or an
upste query using a Switch() function call. The latter is less obvious
but probably more efficient. Try:

UPDATE yourtable
SET [PAT RELATION TO INSD] =
Switch([PAT RELATION TO INSD] = " X", "Self",
[PAT RELATION TO INSD] = " X", "Spouse",
[PAT RELATION TO INSD] IS NULL, "Child",
True, "*ERROR*")

The Switch function takes arguments in pairs; it loops through the
arguments pairwise, and if the first member of the pair is TRUE it
returns the second member and quits. The last line will return the
text string *ERROR* if the column contains (say) " X" (wrong
number of spaces) or other ambiguous or wrong data. If you want those
values left un-updated, use [PAT RELATION TO INSD] instead of
"*ERROR*" to update the field to itself.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps