Question on Nested IIF Statement

J

JOM

I have the following IIf statement in my Query:
CompOnTime: IIf([tblTaxDoc]![TaxDocName]="W2" Or "1099" And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10,"On
Time","OutStd")

How do I add another group to the above i.e., if Document Name 1040's [Rqst
Date] - [Date received] <= 2 "on time", otherwise "outstd"
 
K

KARL DEWEY

If memory serves me correctly you have an error in your statement. I think
it needs to read --
IIf([tblTaxDoc]![TaxDocName]="W2" Or [tblTaxDoc]![TaxDocName]= "1099" And ...

You did not indicate where you want to nest the next IIF statement or do you
want to AND it with some part of the above?

This is the IIF statement structure --
IIF(Test, when true, when false)

For nested IIF it would be like this --
IIF(Test, when true, IIF(Test, when true, when false))
OR
IIF(Test, IIF(Test, when true, when false),when false)

The first one above does not test the second if when the first test is true.
The second always performs the second test if first test is true.

A third nesting would look something like --
IIF(Test, when true, IIF(Test, when true, IIF(Test, when true, when false)))

A fourth like --
IIF(Test, when true, IIF(Test, when true, IIF(Test, when true, IIF(Test,
when true, when false))))
 
O

Ofer

I hope I understood your question, try this

CompOnTime: IIf((([tblTaxDoc]![TaxDocName] = "W2" Or
[tblTaxDoc]![TaxDocName] = "1099") And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10) Or
([tblTaxDoc]![TaxDocName] = "1040's" And [Rqst
Date] - [Date received] <= 2) ,"On Time","OutStd")
 
J

JOM

Thanks Karl, that helped and it actually worked, I have another question
though..
The iif statement has to have another iif statment that is as follows:

CompOnTime:
IIf([tblTaxDoc]![TaxDocStatus]="Complete",IIf([tblTaxDoc]![TaxDocName]="W2"
Or "1099" And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd]<=10,IIf([tblTaxDoc]![TaxDocName]="1040"
And [tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd]<=2,"On
Time","OutStd")),"C.R.")

The thing is that I only want where the status is complete but if the status
is other than Complete it creates a newline which I Named C.R., I don't want
that line, so how do I get rid of it

KARL DEWEY said:
If memory serves me correctly you have an error in your statement. I think
it needs to read --
IIf([tblTaxDoc]![TaxDocName]="W2" Or [tblTaxDoc]![TaxDocName]= "1099" And ...

You did not indicate where you want to nest the next IIF statement or do you
want to AND it with some part of the above?

This is the IIF statement structure --
IIF(Test, when true, when false)

For nested IIF it would be like this --
IIF(Test, when true, IIF(Test, when true, when false))
OR
IIF(Test, IIF(Test, when true, when false),when false)

The first one above does not test the second if when the first test is true.
The second always performs the second test if first test is true.

A third nesting would look something like --
IIF(Test, when true, IIF(Test, when true, IIF(Test, when true, when false)))

A fourth like --
IIF(Test, when true, IIF(Test, when true, IIF(Test, when true, IIF(Test,
when true, when false))))

JOM said:
I have the following IIf statement in my Query:
CompOnTime: IIf([tblTaxDoc]![TaxDocName]="W2" Or "1099" And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10,"On
Time","OutStd")

How do I add another group to the above i.e., if Document Name 1040's [Rqst
Date] - [Date received] <= 2 "on time", otherwise "outstd"
Click to expand...
Click to expand...
 
J

JOM

Thanks Ofer for the answer, however, i have a question on top of what you
posted,
I have another iif statement, I have been trying for it to work, but its not
going well for me,
The Docs have to have a complete status... I have TaxDocStatus(which could
be complete, pending or cancelled...) what I want to include is the
IIf([tblTaxDoc]![TaxDocStatus]="Complete"


CompOnTime: IIf((([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")



Ofer said:
I hope I understood your question, try this

CompOnTime: IIf((([tblTaxDoc]![TaxDocName] = "W2" Or
[tblTaxDoc]![TaxDocName] = "1099") And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10) Or
([tblTaxDoc]![TaxDocName] = "1040's" And [Rqst
Date] - [Date received] <= 2) ,"On Time","OutStd")


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



JOM said:
I have the following IIf statement in my Query:
CompOnTime: IIf([tblTaxDoc]![TaxDocName]="W2" Or "1099" And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10,"On
Time","OutStd")

How do I add another group to the above i.e., if Document Name 1040's [Rqst
Date] - [Date received] <= 2 "on time", otherwise "outstd"
Click to expand...
Click to expand...
 
O

Ofer

Try this, if you want to add a criteria to the other options

CompOnTime: IIf([tblTaxDoc]![TaxDocStatus]="Complete" And
(([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



JOM said:
Thanks Ofer for the answer, however, i have a question on top of what you
posted,
I have another iif statement, I have been trying for it to work, but its not
going well for me,
The Docs have to have a complete status... I have TaxDocStatus(which could
be complete, pending or cancelled...) what I want to include is the
IIf([tblTaxDoc]![TaxDocStatus]="Complete"


CompOnTime: IIf((([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")



Ofer said:
I hope I understood your question, try this

CompOnTime: IIf((([tblTaxDoc]![TaxDocName] = "W2" Or
[tblTaxDoc]![TaxDocName] = "1099") And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10) Or
([tblTaxDoc]![TaxDocName] = "1040's" And [Rqst
Date] - [Date received] <= 2) ,"On Time","OutStd")


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



JOM said:
I have the following IIf statement in my Query:
CompOnTime: IIf([tblTaxDoc]![TaxDocName]="W2" Or "1099" And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10,"On
Time","OutStd")

How do I add another group to the above i.e., if Document Name 1040's [Rqst
Date] - [Date received] <= 2 "on time", otherwise "outstd"
Click to expand...
Click to expand...
Click to expand...
 
J

JOM

I tried this but the problem is that its counting those records that have
cancelled as status... How do I count only those that have completed as
status?

Ofer said:
Try this, if you want to add a criteria to the other options

CompOnTime: IIf([tblTaxDoc]![TaxDocStatus]="Complete" And
(([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



JOM said:
Thanks Ofer for the answer, however, i have a question on top of what you
posted,
I have another iif statement, I have been trying for it to work, but its not
going well for me,
The Docs have to have a complete status... I have TaxDocStatus(which could
be complete, pending or cancelled...) what I want to include is the
IIf([tblTaxDoc]![TaxDocStatus]="Complete"


CompOnTime: IIf((([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")



Ofer said:
I hope I understood your question, try this

CompOnTime: IIf((([tblTaxDoc]![TaxDocName] = "W2" Or
[tblTaxDoc]![TaxDocName] = "1099") And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10) Or
([tblTaxDoc]![TaxDocName] = "1040's" And [Rqst
Date] - [Date received] <= 2) ,"On Time","OutStd")


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have the following IIf statement in my Query:
CompOnTime: IIf([tblTaxDoc]![TaxDocName]="W2" Or "1099" And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10,"On
Time","OutStd")

How do I add another group to the above i.e., if Document Name 1040's [Rqst
Date] - [Date received] <= 2 "on time", otherwise "outstd"
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

Try this

CompOnTime: IIf([tblTaxDoc]![TaxDocStatus]="Complete" And
((([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2)),"On
Time","OutStd")
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



JOM said:
I tried this but the problem is that its counting those records that have
cancelled as status... How do I count only those that have completed as
status?

Ofer said:
Try this, if you want to add a criteria to the other options

CompOnTime: IIf([tblTaxDoc]![TaxDocStatus]="Complete" And
(([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



JOM said:
Thanks Ofer for the answer, however, i have a question on top of what you
posted,
I have another iif statement, I have been trying for it to work, but its not
going well for me,
The Docs have to have a complete status... I have TaxDocStatus(which could
be complete, pending or cancelled...) what I want to include is the
IIf([tblTaxDoc]![TaxDocStatus]="Complete"


CompOnTime: IIf((([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")



:

I hope I understood your question, try this

CompOnTime: IIf((([tblTaxDoc]![TaxDocName] = "W2" Or
[tblTaxDoc]![TaxDocName] = "1099") And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10) Or
([tblTaxDoc]![TaxDocName] = "1040's" And [Rqst
Date] - [Date received] <= 2) ,"On Time","OutStd")


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have the following IIf statement in my Query:
CompOnTime: IIf([tblTaxDoc]![TaxDocName]="W2" Or "1099" And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10,"On
Time","OutStd")

How do I add another group to the above i.e., if Document Name 1040's [Rqst
Date] - [Date received] <= 2 "on time", otherwise "outstd"Tr
Click to expand...
Click to expand...
Click to expand...
 
J

JOM

Thanks Ofer, I tried it but the outcome is still the same

Ofer said:
Try this

CompOnTime: IIf([tblTaxDoc]![TaxDocStatus]="Complete" And
((([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2)),"On
Time","OutStd")
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



JOM said:
I tried this but the problem is that its counting those records that have
cancelled as status... How do I count only those that have completed as
status?

Ofer said:
Try this, if you want to add a criteria to the other options

CompOnTime: IIf([tblTaxDoc]![TaxDocStatus]="Complete" And
(([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")

--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

Thanks Ofer for the answer, however, i have a question on top of what you
posted,
I have another iif statement, I have been trying for it to work, but its not
going well for me,
The Docs have to have a complete status... I have TaxDocStatus(which could
be complete, pending or cancelled...) what I want to include is the
IIf([tblTaxDoc]![TaxDocStatus]="Complete"


CompOnTime: IIf((([tblTaxDoc]![TaxDocName]="W2" Or
[tblTaxDoc]![TaxDocName]="1099") And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=10) Or
([tblTaxDoc]![TaxDocName]="1040" And
[tblTaxDoc]![TxDocDateRcvd]-[tblBorrower]![RqstDateRcvd]<=2),"On
Time","OutStd")



:

I hope I understood your question, try this

CompOnTime: IIf((([tblTaxDoc]![TaxDocName] = "W2" Or
[tblTaxDoc]![TaxDocName] = "1099") And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10) Or
([tblTaxDoc]![TaxDocName] = "1040's" And [Rqst
Date] - [Date received] <= 2) ,"On Time","OutStd")


--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck



:

I have the following IIf statement in my Query:
CompOnTime: IIf([tblTaxDoc]![TaxDocName]="W2" Or "1099" And
[tblBorrower]![RqstDateRcvd]-[tblTaxDoc]![TxDocDateRcvd] <=10,"On
Time","OutStd")

How do I add another group to the above i.e., if Document Name 1040's [Rqst
Date] - [Date received] <= 2 "on time", otherwise "outstd"Tr
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

Please Help on Crosstab Question! 0
Crosstab Query Question 0
Crosstab division question! 0
Not including Weekends and Public Holidays! 4
Error: Query too long therefore will be truncated 2
Calculations on my report! 0
Why is my report crashing 2
Report Error 2

Top