Nested IIf Statements

G

Guest

Hello! I have a query in which I am trying to use multiple nested IIf
statements to populate a new column based on other columns in my query
design. I have typed a select as Expression with nested IIf statements but I
am either getting a syntax error or missing closed parentheses error. Can
anyone tell me what is wrong with this:

Expr1: IIf([PL3]="20" Or "17",[P3],(IIf([PL2]="20" Or
"17",[P2]),(IIf([PL1]="20" Or "17",[P1])),(IIf([PL]="20" Or "17",[P],""))))

The SELECT as EXPRESSION should work like this for me:
Select column PL3 and if it = "20" or "17" then display the value of field P3
if not or null, then
Select column PL2 and if it = "20" or "17" then display the value of field P2
if not or null, then
Select column PL1 and if it = "20" or "17" then display the value of field P1
if not or null, then
Select column PL and if it = "20" or "17" then display the value of field P

Also, at anytime any of the fields may be NULL based on the parameter input
from the first query which this one is based upon. Am I headed in the right
direction with this? Thanks for any help!
 
D

Darren

Try
Expr1: IIf([PL3]="20" Or [PL3]="17",[P3],IIf([PL2]="20" Or
[PL2]="17",[P2],IIf([PL1]="20" Or [PL1]="17",[P1],IIf([PL]="20" Or
[PL]="17",[P],""))))
 
G

Guest

Darren: That works great. Thank you, but when I run the query it asks me for
an input parameter for for each column name. Is it because I have renamed my
columns like this:

PL3:[PRODUCT_LINE3]
P3:[PRODUCT_NAME3] etc....

I get the correct results if I just hit enter with no input, but I really
need to get rid of the input parameter boxes.


Darren said:
Try
Expr1: IIf([PL3]="20" Or [PL3]="17",[P3],IIf([PL2]="20" Or
[PL2]="17",[P2],IIf([PL1]="20" Or [PL1]="17",[P1],IIf([PL]="20" Or
[PL]="17",[P],""))))

la knight said:
Hello! I have a query in which I am trying to use multiple nested IIf
statements to populate a new column based on other columns in my query
design. I have typed a select as Expression with nested IIf statements but
I
am either getting a syntax error or missing closed parentheses error. Can
anyone tell me what is wrong with this:

Expr1: IIf([PL3]="20" Or "17",[P3],(IIf([PL2]="20" Or
"17",[P2]),(IIf([PL1]="20" Or "17",[P1])),(IIf([PL]="20" Or
"17",[P],""))))

The SELECT as EXPRESSION should work like this for me:
Select column PL3 and if it = "20" or "17" then display the value of field
P3
if not or null, then
Select column PL2 and if it = "20" or "17" then display the value of field
P2
if not or null, then
Select column PL1 and if it = "20" or "17" then display the value of field
P1
if not or null, then
Select column PL and if it = "20" or "17" then display the value of field
P

Also, at anytime any of the fields may be NULL based on the parameter
input
from the first query which this one is based upon. Am I headed in the
right
direction with this? Thanks for any help!
Click to expand...
Click to expand...
 
D

Darren

That would be it

la knight said:
Darren: That works great. Thank you, but when I run the query it asks me
for
an input parameter for for each column name. Is it because I have renamed
my
columns like this:

PL3:[PRODUCT_LINE3]
P3:[PRODUCT_NAME3] etc....

I get the correct results if I just hit enter with no input, but I really
need to get rid of the input parameter boxes.


Darren said:
Try
Expr1: IIf([PL3]="20" Or [PL3]="17",[P3],IIf([PL2]="20" Or
[PL2]="17",[P2],IIf([PL1]="20" Or [PL1]="17",[P1],IIf([PL]="20" Or
[PL]="17",[P],""))))

la knight said:
Hello! I have a query in which I am trying to use multiple nested IIf
statements to populate a new column based on other columns in my query
design. I have typed a select as Expression with nested IIf statements
but
I
am either getting a syntax error or missing closed parentheses error.
Can
anyone tell me what is wrong with this:

Expr1: IIf([PL3]="20" Or "17",[P3],(IIf([PL2]="20" Or
"17",[P2]),(IIf([PL1]="20" Or "17",[P1])),(IIf([PL]="20" Or
"17",[P],""))))

The SELECT as EXPRESSION should work like this for me:
Select column PL3 and if it = "20" or "17" then display the value of
field
P3
if not or null, then
Select column PL2 and if it = "20" or "17" then display the value of
field
P2
if not or null, then
Select column PL1 and if it = "20" or "17" then display the value of
field
P1
if not or null, then
Select column PL and if it = "20" or "17" then display the value of
field
P

Also, at anytime any of the fields may be NULL based on the parameter
input
from the first query which this one is based upon. Am I headed in the
right
direction with this? Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I thought so! So I just went backwards to the first subquery and renamed
those columns to [PL3], [P3], etc...
Thanks for your help!!! You made my day!!!

Darren said:
That would be it

la knight said:
Darren: That works great. Thank you, but when I run the query it asks me
for
an input parameter for for each column name. Is it because I have renamed
my
columns like this:

PL3:[PRODUCT_LINE3]
P3:[PRODUCT_NAME3] etc....

I get the correct results if I just hit enter with no input, but I really
need to get rid of the input parameter boxes.


Darren said:
Try
Expr1: IIf([PL3]="20" Or [PL3]="17",[P3],IIf([PL2]="20" Or
[PL2]="17",[P2],IIf([PL1]="20" Or [PL1]="17",[P1],IIf([PL]="20" Or
[PL]="17",[P],""))))

Hello! I have a query in which I am trying to use multiple nested IIf
statements to populate a new column based on other columns in my query
design. I have typed a select as Expression with nested IIf statements
but
I
am either getting a syntax error or missing closed parentheses error.
Can
anyone tell me what is wrong with this:

Expr1: IIf([PL3]="20" Or "17",[P3],(IIf([PL2]="20" Or
"17",[P2]),(IIf([PL1]="20" Or "17",[P1])),(IIf([PL]="20" Or
"17",[P],""))))

The SELECT as EXPRESSION should work like this for me:
Select column PL3 and if it = "20" or "17" then display the value of
field
P3
if not or null, then
Select column PL2 and if it = "20" or "17" then display the value of
field
P2
if not or null, then
Select column PL1 and if it = "20" or "17" then display the value of
field
P1
if not or null, then
Select column PL and if it = "20" or "17" then display the value of
field
P

Also, at anytime any of the fields may be NULL based on the parameter
input
from the first query which this one is based upon. Am I headed in the
right
direction with this? Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try using actual names of the fields.

Also, you might look at using the Switch function. It uses paired
statements - A clause that returns true or false, followed by clause that
determines the value returned. The first time a true/false clause returns
true, the corresponding value is returned. I find it a lot easier to set up
and follow than nested IIF statements.

Switch([Product_Line3] in ("17","20"), [Product_Name3],
[Product_Line2] in ("17","20"), [Product_Name2],
[Product_Line1] in ("17","20"), [Product_Name1],
[Product_Line] in ("17","20"), [Product_Name],
True, Null)


la knight said:
Darren: That works great. Thank you, but when I run the query it asks me
for
an input parameter for for each column name. Is it because I have renamed
my
columns like this:

PL3:[PRODUCT_LINE3]
P3:[PRODUCT_NAME3] etc....

I get the correct results if I just hit enter with no input, but I really
need to get rid of the input parameter boxes.


Darren said:
Try
Expr1: IIf([PL3]="20" Or [PL3]="17",[P3],IIf([PL2]="20" Or
[PL2]="17",[P2],IIf([PL1]="20" Or [PL1]="17",[P1],IIf([PL]="20" Or
[PL]="17",[P],""))))

la knight said:
Hello! I have a query in which I am trying to use multiple nested IIf
statements to populate a new column based on other columns in my query
design. I have typed a select as Expression with nested IIf statements
but
I
am either getting a syntax error or missing closed parentheses error.
Can
anyone tell me what is wrong with this:

Expr1: IIf([PL3]="20" Or "17",[P3],(IIf([PL2]="20" Or
"17",[P2]),(IIf([PL1]="20" Or "17",[P1])),(IIf([PL]="20" Or
"17",[P],""))))

The SELECT as EXPRESSION should work like this for me:
Select column PL3 and if it = "20" or "17" then display the value of
field
P3
if not or null, then
Select column PL2 and if it = "20" or "17" then display the value of
field
P2
if not or null, then
Select column PL1 and if it = "20" or "17" then display the value of
field
P1
if not or null, then
Select column PL and if it = "20" or "17" then display the value of
field
P

Also, at anytime any of the fields may be NULL based on the parameter
input
from the first query which this one is based upon. Am I headed in the
right
direction with this? Thanks for any help!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nested If conversion to Select case 5
First value below zero in a column 1
Nested IF or IIF Formula Help 0
nested iif function 4
Group by month 2
How to Combine Four Fields into One with a Query 1
Multiple IIf's 6
iif, or, else? 3

Top