Strange formula problem

[Mikilu]

I have a very strange dilemma with a simple formula I’m using in a report. I created a report that outputs number of sales for different products and they are grouped by category. In group ‘A’, I have give products and in Group ‘B’ I have seven products and grouping goes upto ‘F’. The sales are displayed downwards in one column. At times certain groups have no sales and remain blank in the report. I created group header and footer and in the footer of each grouping, I total the sales by using the
=IIf(IsNull([Sales]),0,Sum([Sales])) formula. For some reason, the first group on the first page sums just fine and the second group on the second page (group ‘B’) sums up as zero even though there are sales in every product. Why in the world would this formula work differently from page to page? Please shade some light for me. Thanks and happy Thanksgiving.

 

[Andrew Fergus]

Hi
Try using the following instead :
=Sum(Nz([Sales])).
The Nz function converts the null values to zero.
Does it make any difference?
HTH, Andrew

 

[Mikilu]

Excellent!. Works like a charm. Thanks a lot.