How to update a field with multiple values with one SQL statement?

[saagardn]

I am using Access 2007 on an XP machine. Is it possible to update one field with multiple values using a single SQL statement? I have tired to do this with the query builder, but keep getting a "duplicate output destination" error (I think it is error #3063). This is the code that Access "builds" for me, but does not run:

UPDATE Sept_8_2008_GRE SET Sept_8_2008_GRE.[Chem Interest Area 1] = "b", Sept_8_2008_GRE.[Chem Interest Area 1] = "b ,p", Sept_8_2008_GRE.[Chem Interest Area 1] = " "
WHERE ( (((Sept_8_2008_GRE.[Chem Interest Area 1])="202" Or (Sept_8_2008_GRE.[Chem Interest Area 1])="206") And (Sept_8_2008_GRE.[Chem Interest Area 1])="222" And ((Sept_8_2008_GRE.[Chem Interest Area 1])="301" Or (Sept_8_2008_GRE.[Chem Interest Area 1])="399") ));

I have also tired this, but now I get a syntax error:
UPDATE Sept_8_2008_GRE SET Sept_8_2008_GRE.[Chem Interest Area 1] = CASE WHEN [Chem Interest Area 1] IN ("202" OR "206") THEN [Chem Interest Area 1]=("b")
WHEN [Chem Interest Area 1] IN ("222") THEN [Chem Interest Area 1]=("b, p") WHEN [Chem Interest Area 1] IN ("301" OR "399") THEN [Chem Interest Area 1]=(" ") END;

Can anyone help? I need to be able to do this update in one step!

Thanks,
Sara

[saagardn]

I am using Access 2007 on an XP machine. Is it possible to update one field with multiple values using a single SQL statement? I have tired to do this with the query builder, but keep getting a "duplicate output destination" error (I think it is error #3063). This is the code that Access "builds" for me, but does not run:

UPDATE Sept_8_2008_GRE SET Sept_8_2008_GRE.[Chem Interest Area 1] = "b", Sept_8_2008_GRE.[Chem Interest Area 1] = "b ,p", Sept_8_2008_GRE.[Chem Interest Area 1] = " "
WHERE ( (((Sept_8_2008_GRE.[Chem Interest Area 1])="202" Or (Sept_8_2008_GRE.[Chem Interest Area 1])="206") And (Sept_8_2008_GRE.[Chem Interest Area 1])="222" And ((Sept_8_2008_GRE.[Chem Interest Area 1])="301" Or (Sept_8_2008_GRE.[Chem Interest Area 1])="399") ));

I have also tired this, but now I get a syntax error:
UPDATE Sept_8_2008_GRE SET Sept_8_2008_GRE.[Chem Interest Area 1] = CASE WHEN [Chem Interest Area 1] IN ("202" OR "206") THEN [Chem Interest Area 1]=("b")
WHEN [Chem Interest Area 1] IN ("222") THEN [Chem Interest Area 1]=("b, p") WHEN [Chem Interest Area 1] IN ("301" OR "399") THEN [Chem Interest Area 1]=(" ") END;

Can anyone help? I need to be able to do this update in one step!

Thanks,
Sara

It looks like MS Access can't use CASE ... but has a function called SWITCH. This will run, but the result is a blank field. Not sure what I am doing wrong.
UPDATE Sept_8_2008_GRE SET
ChemInterestArea1 = SWITCH(ChemInterestArea1 IN ("202", "206"), "b",
ChemInterestArea1="222", "b, p" ;