Report error

  • Thread starter Thread starter Mick Horan
  • Start date Start date
M

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
 
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
Click to expand...

Look up the Nz) function in VBA help.

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