Report error

[Mick Horan]

Hi,



I'm trying to create a report that totals the data in the report by first
multiplying the data in each column by the column number (1 to 7) and then
divide that result by another column total.



The problem is that I can have an all ZERO amount in each of these columns
which causes the report to print "#Num!" in my total line.



Here is the data in my total line. This works fine as long as I have data.



=(Sum([BLev1])*1+Sum([BLev2])*2+Sum([BLev3])*3+Sum([BLev4])*4+Sum([BLev5])*5
+Sum([BLev6])*6+Sum([BLev7])*7)/Sum([SumBLtotal])



I'm trying to use the IIF statement but can't get it to work.



Does anyone have an idea as to what I can use to fix this.



Thanks for the help. Mick

 

[fredg]

Hi,

I'm trying to create a report that totals the data in the report by first
multiplying the data in each column by the column number (1 to 7) and then
divide that result by another column total.

The problem is that I can have an all ZERO amount in each of these columns
which causes the report to print "#Num!" in my total line.

Here is the data in my total line. This works fine as long as I have data.

=(Sum([BLev1])*1+Sum([BLev2])*2+Sum([BLev3])*3+Sum([BLev4])*4+Sum([BLev5])*5
+Sum([BLev6])*6+Sum([BLev7])*7)/Sum([SumBLtotal])

I'm trying to use the IIF statement but can't get it to work.

Does anyone have an idea as to what I can use to fix this.

Thanks for the help. Mick

Look up the Nz) function in VBA help.

=Sum(Nz([Blev6],0) * 1 + Sum(Nz([Blev2],0) * 2 + etc.