Summing w/ Null values on Reports

[DLeit]

I am trying to sum Subreports on a Master report. If I have a field come back as a null value in any of the subreports, I get the wonderful #Error. I am fine as long as there are no Null values. Is there a way to set my null values on my subreports to zero or tell the summing fuction that if it sees a Null value to count it as zero?

 

[Trevil]

Why not change your report query to something like:
.. IIf([nbrfld] Is Null,0,[nbrfld]) AS MyNbr

This gets rid of the problem at the source.

"Hmmm, it worked when I tested it....

 

[dhookom]

DLeit,
Is your issue caused by subreports that don't return any records? This doesn't have anything to do with Null. It has everything to do with not having any records to sum.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[DLeit]

Yes, you are correct this is an issue with subreports that don't return any records. How can I get my SUM to work when there are no records to sum? Do I have to work back into the original Query to set something up to return a record populated with a zero? You are right now that I think about it, it has nothing to do with Null values, and that is why my IIf statements weren't working.

 

[dhookom]

Use expressions in your main report like:
=IIf(srptMySub.Report.HasData, srptMySub.Report.txtTotal, 0)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]