Nested IIf Statements

  • Thread starter Thread starter Guest
  • Start date Start date
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!
 
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],""))))
 
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...
 
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...
 
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...
 
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
IIF Statement In Query Doesn't Work Right 3
Multiple IIf's 6
Nested IIF Statement in a query 4
Group by month 2
How to Combine Four Fields into One with a Query 1

Back
Top