Sum Nested IIFs in Access 2010

[JHamrick]

I am trying to sum the following and keep getting either wrong totals or a blank.

=Sum(IIf([Chute#1]="CBOL - N/A",[Pallet#1Lbs]/2000))+(Count(IIf([Chute#2]="CBOL - N/A",[Pallet#2Lbs]/2000)))+(Count(IIf([Chute#3]="CBOL - N/A",[Pallet#3Lbs]/2000)))+(Count(IIf([Chute#4]="CBOL - N/A",[Pallet#4Lbs]/2000)))+(Count(IIf([Chute#5]="CBOL - N/A",[Pallet#5Lbs]/2000)))+(Count(IIf([Chute#6]="CBOL - N/A",[Pallet#6Lbs]/2000)))


Basically, I have a table with 6 fields (Chute#1, Chute#2, etc) that when a record equals "CBOL - N/A" it will SUM the weight stored in another field (Pallet#1Lbs, Pallet#2Lbs, etc.). Is there a better way?

[Rabbit]

You say sum but you use count a lot. Also, all your iif() functions are missing the false parameter. It shouldn't even run, it should be giving you an error.

[sierra7]

I don't understand the question. Sometimes you are using SUM and sometimes COUNT.

Are you doing this in a Form or a Report?

Does Pallet#1 correspond only to Chute#1, Pallet#2 to Chute#2 etc. Please confirm.

I think in essence you want the folowing but they are not nested;

=(
IIf(Chute#1="CBOL - N/A", Pallet#1Lbs,0) +
IIf(Chute#2="CBOL - N/A", Pallet#2Lbs,0) +
IIf(Chute#3="CBOL - N/A", Pallet#3Lbs,0) +
IIf(Chute#4="CBOL - N/A", Pallet#4Lbs,0) +
IIf(Chute#5="CBOL - N/A", Pallet#5Lbs,0) +
IIf(Chute#6="CBOL - N/A", Pallet#6Lbs,0) )/2000
 

I think this is what is you mean but I'm not clear why you used COUNT.
S7

[JHamrick]

Sorry for the confusion. Yes I had taken out the counts after I posted this question (sorry). I have even added each field (or expression) in increments to test where it stops working. I can get to this point and it calculates fine.

=Sum(IIf([Chute#1]="CBOL - N/A",[Pallet#1Lbs]/2000))+(IIf([Chute#2]="CBOL - N/A",[Pallet#2Lbs]/2000))

But when I add the next field (Chute#3) the results are blank. Based on my data, this is where the first false it should encounter as there is no "CBOL - N/A" in the Chute#3 field.

=Sum(IIf([Chute#1]="CBOL - N/A",[Pallet#1Lbs]/2000))+(IIf([Chute#2]="CBOL - N/A",[Pallet#2Lbs]/2000))+(IIf([Chute#3]="CBOL - N/A",[Pallet#3Lbs]/2000))


Yes, this is in a report. Yes, Pallet#1 field is the weight of Chute#1 and so on. When Chute#1 = "CBOL - N/A" and so on, I want to sum the weights together.

[sierra7]

Hi
The IIF() function consists of a 'test', then a value for if the result is true, and then a second value for if the result is false.

The zeros (0) in the code I posted were for when Chute#1 (etc) is not equal to "CBOL - N/A", i.e false. All values must be numeric if you want to add them.

I still can't see why you want to nest the IIF()'s on this occasion and you don't seem to be doing so in your sample code.

A SUM() will only work in the Footer section of a report.

So far I am assuming the are 12 (or more) fields in your table (or query) i.e. 6 Chutes & 6 Weights, one weight per chute. If you are trying to sum multiple weights per chute we have a problem.
S7

[JHamrick]

@sierra7
---
Your suggestion worked for the weight totals! I put a Sum in front since it was in the footer section and it works like a champ. I assumed that since I used multiple IIFs in the statement that they were "Nested".

Now how can I "Count" the number of times "CBOL - N/A" is listed in all the 6 fields? Can I use the same code and put a count instead of a sum? When I try it returns "0" when there it should return "6".

[sierra7]

Thank goodness for that!

To do the count try copying the 'control' (i.e. the box with the current formula), then change the 'Pallet weight' to 1

IIf(Chute#1="CBOL - N/A", 1,0) + 
 

The formula should sum the ones and give you your count!
S7

[JHamrick]

Awesome it works! Thanks. It just takes another set of eyes to see through your self-induced confusion.

[NeoPa]

FYI: IIf() in SQL (which is what you're working with within Control Sources) requires only the first two parameters. The False value, if omitted, defaults to False, which has a numeric value of zero (0) anyway.