leaving values as they are

I have a short question: I need a command to make some values stay the same in an update query with iff statement. Some background about my problem: I have an update query which is pretty long with 19 iff statements. Iff statements look like this;

UPDATE  1963
SET ProductCode = IIF(
        (ProductCode >=  8410 and ProductCode <= 8421),
        18,   and so on...

So basically I am updating a field according to the values in the same field. When I run the code Access 2003 gives "expression too complex" error. Since I cannot run all the code at once, I thought that I could divide the code into two parts and run separately. But once I run the first part, since this is an iff statement, the values which are not specified in the first part of the code become null. Theref开发者_StackOverflow中文版ore I cannot run the second part of the code since I cannot specify the range of the Product code for the second part(they all become null) So I need a way to make the unspecified values stay the same as before in the first part, so I can run the second part to complete the code. That is I need something saying if (ProductCode >= 8410 and ProductCode <= 8421) set product code=18, otherwise don't change the value. Thanks in advance.

---

If you have more than a few If statements, it is time to look at the approach you are using. In this case, I suggest you use a table that includes the current value and the value you wish to change to, as was suggested in your previous post. You do not need a range if it does not suit, a simple one to one match is not difficult.

New Table:

Code  ChangeTo
8410    18
8321    18
7865     1

Your query would then be something like:

UPDATE MyTable 
INNER JOIN NewTable 
ON MyTable.Code = NewTable.Code
SET MyTableCode = NewTable.ChangeTo

---

I would move the IIF conditions to the where clause and make the ProductCode high and low restrictions and the new values into parameters. Then run the query 19 times in code with different parameters. If you had a SQL Server back end, there would be a more elegant way to do this, but for a pure Access solution you'll have better luck keeping the query logic simple and making up the difference with some VBA code.

---

Try to create a temporary table to set the new values, then do the update regarding the temporary table.

Create new table [NewTable] based on the same structure of the old one.[without Primary Key]

Insert into NewTable (ID,field1,field2…..)
Select  ID,  iif(fiedl1condition,    trueValue,falseValue),………
From OldTable 

UPDATE OldTable 
INNER JOIN NewTable 
ON OldTable .ID= NewTable.ID
SET Field1 = NewTable.Field1
Field2 = NewTable.Field2
Field3 = NewTable.Field3

Drop TABLE NewTable