using multiple IIf commands

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I am trying to update the value of field B, however, the
value to be input is dependent on the value of Field A.

Given that field A can have any one of 10 values I want to
write a query that says "Set Field B value to x if Field A
value =0, Set Field B value to y if Field A value = 1, and
so on.

Any ideas on how to do this ?

Thanks
 
Try something along the lines of

UPDATE YourTable
SET fieldB = IIf(fieldA=0,x,IIf(fieldA=1,y,IIf(etc

Hope This Helps
Gerald Stanley MCSD
 
Thanks very much
-----Original Message-----
Try something along the lines of

UPDATE YourTable
SET fieldB = IIf(fieldA=0,x,IIf(fieldA=1,y,IIf(etc

Hope This Helps
Gerald Stanley MCSD
.
Click to expand...
 
Take a look at the Switch and Choose functions. If the values of field A are
consecutive then you can use the Switch function.

Set FieldB = Switch(FieldA+1,Value1, Value2, ..., Value10)

If the values are not consecutive then use the Choose function or better yet
write a function.

BEST Solution is to add a table with two columns. Column 1 has the values of
FieldA and Column 2 has the equivalent values. This way all you ever do is add
the table to your queries and join the table to the value in field a to see the
value in field b. If you absolutely have to store the value, then use an update
query based on the joined tables.

One BIG advantage of the table solution and joined query solution: If you need
to add another A value and B equivalent, you just add it to the table. No
queries or code to change when you add an 11th choice, or a 12th choice, or you
decide that the 1st choice of A needs to have a different equivalent value.
 
Back
Top