Calculations in report footer - missing data

[tech84]

I'm having trouble with a report that contains 5 subreports. I'll start off by showing you the fields involved, then explain the issue.

In the report footer of each of the 5 subreports, there is a text box called txtSectionXAvg (the X will be 1 - 5, depending on which subreport it's in), and the control source is: =Avg([AvgOfQuestion_Response]). AvgOfQuestion_Response is calculated by a query. There is one query for each subreport.

In the report footer of the main report, there are 5 text boxes called txtSectionXAvg, and the control source for each is =srptSectionX.Report![txtSectionXAvg]. All five of these text boxes are not visible.

Also in the report footer of the main report is another text box called txtReportAvg, and the control source is =([txtSect1Avg]+[txtSect2Avg]+[txtSect3Avg]+[txtSect4Avg]+[txtSect5Avg])/5.

This is pretty crude, I suppose, but it's the only way I could figure out how to get an average of all of the subreports in the footer of the main report. You may already see what my problem is (besides bad code!). For some of the reports generated, there is no data at all for subreport 4. Hence, the txtSection4Avg text box contains #Error. This, in turn, causes the same message to appear in the txtReportAvg text box.

So here's my question: is there a way to calculate that average if there is no data for subreport 4? I've searched through the Access forums for a similar question, and came up empty. The Access help files aren't much better. Oh yeah... I'm using Access 2002 SP2.

Any help you can give me would be greatly appreciated!

Thanks,

Mike

 

[PaulBricker]

The Nz function should be able to help. Either in each subreport or in the Main calculation.

=(Nz(txtSect1Avg],0)+ Nz([txtSect2Avg],0)+ Nz([txtSect3Avg],O)+ Nz([txtSect4Avg],0)+ Nz([txtSect5Avg],0))/5


If one of the textboxes is null the value becomes 0 for the calculation. You should be able to do it here but if it throws up an error then you can try using it in the subreports.

Paul

 

[tech84]

Thanks! You know, I looked up Nz in the Access help files because I thought that may be the ticket, but it came back with nothing. Piece of junk.

Thanks again, Paul!

Mike

 

[WannaBeExpert]

OK Tech84,

Before I try to make some suggestion, may I know a simple words to explain your project.
On your MDB should be have parent and child table, is it right?
Can u give me a sample, 2 records of parent and few child record on each?

I'm waiting for...

 

[dhookom]

Nz() will not work if the subreport has no records. You must use the HasData property of the subreports. For instance:
=IIf(srptSectionX.Report.HasData, srptSectionX.Report![txtSectionXAvg],0)
If the srptSectionX doesn't contain any records then this will return 0.
I'm not sure how you want to handle the average of all if there are no records in one or more subreport. If you don't want to include empty subreports, then your denominator will not be "5". It will have to be an expression like:
Abs(txtSection1Avg<>0 + txtSection2Avg <> 0+...)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[tech84]

Duane,

You're correct... I tried the Nz() and it didn't work. (I still appreciate your help, though, Paul!)

So I was able to incorporate the IIf statement as the control source for the txtSectionXAvg text boxes successfully, but where do I find out more about the Abs() function? As with Nz(), I can't find anything in the Access Help files. I have an Access book, but it doesn't contain this function either. I tried it out like you have it entered in your post, but no matter what the averages are from the subreports, the result of the Abs() funcion is always 1. Do you have any idea why I would get that result, no matter what?

Your help so far has been priceless! Thank you so much.

Mike

 

[dhookom]

To find help on functions, open any module window and then open Help. Abs() finds the absolute value of a number so true/-1 becomes 1.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[tech84]

Ah! I feel like a dodo! So now that I know what Abs means, I'm in business. Here's the equation that seems to be working now:

=([txtSect1Avg]+[txtSect2Avg]+[txtSect3Avg]+[txtSect4Avg]+[txtSect5Avg])/(Abs([txtSect1Avg]<>0)+Abs([txtSect2Avg]<>0)+Abs([txtSect3Avg]<>0)+Abs([txtSect4Avg]<>0)+Abs([txtSect5Avg]<>0))

Thank you all for your help! Thank you, too, WannaBeExpert. Sorry I didn't give you a chance to share your Access prowess.

Duane, I appreciate your showing me where to go to look up functions in the Help files, too.

Mike

 

[tech84]

Oh yeah... just for clarification, the equation in my last post is the control source for the txtReportAvg text box.

Mike