Trying to nest IIf statements in a report

R

Roland

I am trying to run a report that generates invoices for various households,
currently only 1 of the households requires their invoice to be split by
department.
In the department header, I have the following textbox
=IIf([HouseholdNumber]=1,"Department: " & [Department],"")

I have now been asked that a second household also wants their invoice split
by department and am having difficulty nesting 2 IIf statements and have
tried the following to no avil
=IIf([HouseholdNumber]=1,"Department: " & [Department],"") And
IIf([HouseholdNumber]=7,"Department: " & [Department],"")
=IIf([HouseholdNumber]=1,"Department: " & [Department],"") Or
IIf([HouseholdNumber]=7,"Department: " & [Department],"")
=IIf([HouseholdNumber]=1,"Department: " & [Department],""),
IIf([HouseholdNumber]=1,"Department: " & [Department],"")
=IIf([HouseholdNumber]=1,"Department: " IIf([HouseholdNumber]=7,"Department:
" & [Department],"")

In the department footer, I also need to update the following textbox's to
also show HouseholdNumber 7
=IIf([HouseholdNumber]=1,"Total Payment Due (exc. VAT):","")
=IIf([HouseholdNumber]=1,"VAT @ " & Format([VatRate],"#.#%") & ":","")
=IIf([HouseholdNumber]=1,"Total Payment Due (inc VAT):","")
=IIf([HouseholdNumber]=1,Sum(Vehicle_Total(Monthly_Total([MonthlyRental]+[AccessoryCharge],[VatRate]),[TotalMonths])),"")
=IIf([HouseholdNumber]=1,([txtDeptMinusVAT]*[VatRate]),"")
=IIf([HouseholdNumber]=1,[txtDeptMinusVAT]+[txtDeptVAT],"")

Many thanks in advance for any help/advice
 
A

akphidelt

You're close... but try nesting the 2nd if statement in to the FALSE section
of the first one such as

IIf([HouseholdNumber]=1,"Department: " &
[Department],IIf([HouseholdNumber]=7,"Department: " & [Department],""))
 
A

akphidelt

Oh yea, and if you want to check for multiple conditions then put those
conditions together.

Like

=IIf([HouseholdNumber]=1 Or [HouseholdNumber]=7,"Department: " &
[Department],"")
 
R

Roland

I've tried both of your suggestions, (as well as trying to use IN (1,7) as
suggested elsewhere), but when I try and run the report for all Households
the report/invoice does not run and I get the following message:-
"This expression is typed incorrectly, or it is too complex to be
evaluated......."

When I run the report for just Household 7, it prints but does not split the
departments. When I run it for Household 1, it prints correctly with the
departments split.

FYI, I did not originally create the report and associated queries, but as
far as I can tell there is nothing to indicate any restrictions for just
household1.
Scrub that, just found an Event Procedure that I now need to adjust and I
think the important line that I need to change is:-
If Me![txtHHNo] = 1 Then
but change it to what?

akphidelt said:
Oh yea, and if you want to check for multiple conditions then put those
conditions together.

Like

=IIf([HouseholdNumber]=1 Or [HouseholdNumber]=7,"Department: " &
[Department],"")

Roland said:
I am trying to run a report that generates invoices for various households,
currently only 1 of the households requires their invoice to be split by
department.
In the department header, I have the following textbox
=IIf([HouseholdNumber]=1,"Department: " & [Department],"")

I have now been asked that a second household also wants their invoice split
by department and am having difficulty nesting 2 IIf statements and have
tried the following to no avil
=IIf([HouseholdNumber]=1,"Department: " & [Department],"") And
IIf([HouseholdNumber]=7,"Department: " & [Department],"")
=IIf([HouseholdNumber]=1,"Department: " & [Department],"") Or
IIf([HouseholdNumber]=7,"Department: " & [Department],"")
=IIf([HouseholdNumber]=1,"Department: " & [Department],""),
IIf([HouseholdNumber]=1,"Department: " & [Department],"")
=IIf([HouseholdNumber]=1,"Department: " IIf([HouseholdNumber]=7,"Department:
" & [Department],"")

In the department footer, I also need to update the following textbox's to
also show HouseholdNumber 7
=IIf([HouseholdNumber]=1,"Total Payment Due (exc. VAT):","")
=IIf([HouseholdNumber]=1,"VAT @ " & Format([VatRate],"#.#%") & ":","")
=IIf([HouseholdNumber]=1,"Total Payment Due (inc VAT):","")
=IIf([HouseholdNumber]=1,Sum(Vehicle_Total(Monthly_Total([MonthlyRental]+[AccessoryCharge],[VatRate]),[TotalMonths])),"")
=IIf([HouseholdNumber]=1,([txtDeptMinusVAT]*[VatRate]),"")
=IIf([HouseholdNumber]=1,[txtDeptMinusVAT]+[txtDeptVAT],"")

Many thanks in advance for any help/advice
Click to expand...
Click to expand...
 
Top