You don't mention where you are using this construct.
I find nested IIF() to be a pain to follow.
So instead you may want to consider the
switch() function
In a calculated query field you can do something like this:
- zs: Nz(
-
Switch([people_pk]=6,"six",
-
[people_pk]=7,"seven",
-
[people_pk]=8,"eight"),
-
"No Match")
Here I use the NZ() to act like a case-else
The same will hold true in a form or report text field, simply replace the "zs:" with an equal-sign.
So in your case it would appear to be (in the query):
- CalcIncome: Nz(
-
Switch([LocationsT].[SiteID]=1,[Totalincome].[castleblackincome],
-
[LocationsT].[SiteID]=2,[Totalincome].[Torontoincome],
-
[LocationsT].[SiteID]=3,[Totalincome].[Edmontonincome]),
-
0)
replaceing "CalcIncome:" with "=" for use in a form/report control.
In this case if your conditions fail, thus returning a null value, I've trapped this, so-to-speak, to return a zero value.