Null value subreport?

[valkyry]

Hello,
I can't seem to figure out how to give a 0 value if the subreport results in a null value.

I have a report with several subreports which are based off of queries.

Some months there isn't any data in one or more of the subreports and I need it to return a 0 value instead of a null.

REASON: there's a grand total in sections where it takes 2 or more subreports totals for the grand total. It results in #Error# if one or more of the subreport has a null value.

I've tried the IIF([Value],is null,0,[value]) but it is not returning a 0 in the subreport. It still returns a #Error#.

Although depending on the query, it does work but if the result of the entire query is nothing, is where I am lost in all this.

I hope I made sense and someone can help me here.

 

[THWatson]

valkyry
You have to test the subreport for data, using the HasData property.

In your main report, have an unbound text box with the following as its control source...

=IIf([YourSubReportName].[Report].[HasData]=-1,[YourSubReportName].[Report]![TotalTextBoxInSubreport],0)

You will have to do this for each subreport.

Tom

 

[valkyry]

Thank you!
Ok, well, it results in a 0 as the subreport is currently returns a null value ... but how do I get my grandtotal that pulls the total from this subreport to calculate?

the grand total uses the name of the subreport and the total field.

I can't name this text box the same name as the existing subreport.

Did that make sense?

 

[THWatson]

valkyry
You need an unbound text box for each of your subreports, and then an additional unbound text box in which to calculate your total.

For example...
Let's call the first text box txtSub1. It would have the following as its control source...

=IIf([YourSubreport1Name].[Report].[HasData]=-1,[YourSubReport1Name].[Report]![TotalTextBoxInSubreport1Footer],0)

Let's call the second text box txtSub2. It would have the following as its control source...

=IIf([YourSubreport2Name].[Report].[HasData]=-1,[YourSubReport2Name].[Report]![TotalTextBoxInSubreport2Footer],0)

The third text box, to get your grand total, and let's call it txtGrandTotal would have as its control source...

=[txtSub1]+[txtSub2]

If you have more than 2 subreports, then create as many additional text boxes for the subreports as you need, and add the names of those text boxes into the control source of the txtGrandTotal box.

Hope that helps.

Tom