how do you nest IIF statement in query?

[Donna]

Hello,
I've seen it done, but have forgotten how to write it out... I have an
action query (Make Table qry), based on a single table, I have a field named
ExpenseElement. It is a number field, integer. I have set the criteria to
look for 3 expense elements: 111 or 120 or 900. I want to assign a new
number to all the records found, meeting this criteria. For instance: create
a new expense element from 111 to 112; from 120 to 121, and from 900 to 901.
I created a new field in the query, named it NewEE:IIF [this is where I want
to put the nested Iif statements]. I used the operator "OR" between the 3
IIF statements, but the results I got were a True value of -1. It did not
replace the EE numbers as I wanted. (I don't know how to write SQL from
scratch) I wrote something similar to this: iif ([EE]=111,112) OR IIF
([EE]=120, 121) OR IIF ([EE]=900, 901). As I mentioned, it returned a True
value for each of the records, but it did not assign the new numbers I
wanted it to. What am I doing wrong?

Thanks for your advice, Donna

 

[Guest]

Use the followings :-

iif([EE]=111,112, iif([EE]=120,121,iif([EE]=900,901,whatever you want
return)))

 

[Baz]

Hello,
I've seen it done, but have forgotten how to write it out... I have an
action query (Make Table qry), based on a single table, I have a field named
ExpenseElement. It is a number field, integer. I have set the criteria to
look for 3 expense elements: 111 or 120 or 900. I want to assign a new
number to all the records found, meeting this criteria. For instance: create
a new expense element from 111 to 112; from 120 to 121, and from 900 to 901.
I created a new field in the query, named it NewEE:IIF [this is where I want
to put the nested Iif statements]. I used the operator "OR" between the 3
IIF statements, but the results I got were a True value of -1. It did not
replace the EE numbers as I wanted. (I don't know how to write SQL from
scratch) I wrote something similar to this: iif ([EE]=111,112) OR IIF
([EE]=120, 121) OR IIF ([EE]=900, 901). As I mentioned, it returned a True
value for each of the records, but it did not assign the new numbers I
wanted it to. What am I doing wrong?

Thanks for your advice, Donna

It would be pretty yucky using Iif. The Switch function would be much
simpler:

Switch([EE]=111,112,[EE]=120,121,[EE]=900,901)

Or, if you always add 1, why not simply:

[EE] + 1

 

[Guest]

Just for the sake of argument, you could do this also:

iif ([EE] in (111, 120, 900), [EE]+1, [EE])

Good Luck!

 

[Donna]

Thank you. This is the syntax I was looking for. I was using a bogus number
and incrementing by 1 for the sake of simplicity. It works great and I
appreciate your timely answer to the question. It has taken me a few days to
get back to post a reply. What a busy week! Thanks Woo Mun Foong

Use the followings :-

iif([EE]=111,112, iif([EE]=120,121,iif([EE]=900,901,whatever you want
return)))

Hello,
I've seen it done, but have forgotten how to write it out... I have an
action query (Make Table qry), based on a single table, I have a field
named
ExpenseElement. It is a number field, integer. I have set the criteria to
look for 3 expense elements: 111 or 120 or 900. I want to assign a new
number to all the records found, meeting this criteria. For instance:
create
a new expense element from 111 to 112; from 120 to 121, and from 900 to
901.
I created a new field in the query, named it NewEE:IIF [this is where I
want
to put the nested Iif statements]. I used the operator "OR" between the 3
IIF statements, but the results I got were a True value of -1. It did not
replace the EE numbers as I wanted. (I don't know how to write SQL from
scratch) I wrote something similar to this: iif ([EE]=111,112) OR IIF
([EE]=120, 121) OR IIF ([EE]=900, 901). As I mentioned, it returned a
True
value for each of the records, but it did not assign the new numbers I
wanted it to. What am I doing wrong?

Thanks for your advice, Donna