Report totals says, "#Error" when i print. But looks perfectly fine in Printview

[govnah]

Hi mighty fine people,

I have a report with two sub-reports attached. The Main report calculates the totals of the two sub-reports.

The problem i am having is that when i open the report, it looks perfectly fine; the totals are calculated perfectly. But when i actually print the report, the total field shows #Error on the printed paper.

I was reading around and it looks like, and i've verified that the reason for this is because one of my sub-forms has null record set. When both sub-reports generate records, it prints the totals without "#Error".

One sub-report will be empty from time to time.

How can i allow my totals to still calculate with just one sub-form when the other is empty?

Thank You

[NeoPa]

That depends very heavily on how you're doing what you're doing. Perhaps if you give some details of what is erroring and where we can help you.

[dsatino]

Definitely need more details, but in general you need to test for a null result before the report prints(preview) and tell it what to do if the result is null.

[NeoPa]

Reports have an event On No Data that can be used to test this. The stub of the event procedure looks like :

Private Sub Report_NoData(Cancel As Integer)

End Sub

Setting Cancel to True before exiting it will cause the report not to run. This may not be what you need. It's hard to tell from the little information you've shared.

[govnah]

Thank You Guys for taking time out to help.

So sorry, for the lack of details.

I thought of a different workaround to the problem. I decided to add a default record to the sub-report so that there is always data in the sub-report to show. Also i think the problem was caused because the results from the sub-reports were not calculated with Nz(). I didn't know Nz(Sum(Price),0 ) is a way better solution than Sum(Price).

I have definitely learnt a new trick from that experience.



However, Sir NeoPa,
Regarding On No Data event.

If i had a sub-report that sometimes produces No Data, could i use this event to not include the sub-report and still be able to calculate the Grand Total without Error?

Example:

My main report has a final price from calculating the total prices from the two sub-reports.

MainReport:
txtGrandTotal = SubRpt1_Total + SubRpt2_Total


If SubRpt1_Total has No Data, will it still be possible to calculate txtGrandTotal?


Thank You

[NeoPa]

I'm sorry to disappoint, but my experience there is limited. I only have one example in my whole portfolio of using a subreport. I never reference any of the controls therein from the main report either. Unfortunately I cannot even play around as the data has gone so I cannot even run it now :(

My guess though, is that a missing subreport would result in your formula failing due to the invalid reference.

Another thing to consider is what you actually want from this. When a report is run in a standard way it makes sense to abort if there is nothing to show. That is all stand-alone. When a report is integrated into another report as a subreport, then the situation is different. Failing to run the subreport doesn't allow the operator to start again as the main report is run anyway. All that happens is you are left with an ugly hole.

What would you want it to do in those circumstances? That's the question you need to ask yourself first. I expect the answer would be to run the report and include the total value, that must evaluate to zero rather than a Null or #Error result.

When you know what your plan is, we can help you to execute that plan.

[govnah]

Thank you NeoPa,

Yes please, i would like it to give the sub-report's total result which evaluates to zero when there is no data.

Quick question, when the sub-report has no data, is there literally a hole left in the main report? or the hole will be filled with the other sub-report?

Example:
On the main report, subRpt1 has no data therefore it doesn't run. Will subRpt2 move up and occupy the space that subRpt1 would have taken?

Thank You

[NeoPa]

You want the report to run and have a control whose value is zero. In that case I suggest a report footer be used that contains a control that has :

=Nz(Sum([txtVal]),0)

or such like as the formula. Again, I'm afraid I'm not in a position to check this out for you as I have no materials, but try that and see what occurs.

As for the report being cancelled, I expect the result would depend on the design of your main report and how the subreport control is set up. You could probably do it either way, but I'd need to play to see how.

Sorry, I did mention this wasn't one of my more experienced areas :(