#Error in Linked Text Box 1

[floydpepper]

Ladies and Gents,
I have a report with several subreports in it. I have a "total" from each of the subreports linked to its own individual text box in the "main report". The problem that I am having is that when a subreport has no data and thus does not appear, the text box that is linked to that subreport on the main report reads #Error. Can I make this just say 0 instead? Thanks.

Floyd P

 

[THWatson]

Floyd
You have to use the HasData property to test for data in each subreport.

An example from an Invoice report of mine...
=IIf([rptInvoiceDetails].[Report].[HasData]=-1,[rptInvoiceDetails].[Report]![Text11],0)

Hope that helps.

Tom

 

[floydpepper]

Thanks. Where would I insert this code?

 

[THWatson]

Floyd
You said that you had a text box for each of the subreport totals. Use this code as the Control Source in each of the text boxes.

Just to explain further, the "-1" is the same as True. You can look at Help for the HasData property for further information.

So your particular text boxes, in which you want subreport totals, would be...
control source for first subreport...
=IIf([YourFirstSubreportName].[Report].[HasData]=-1,[YourFirstSubreportName].[Report]![YourFirstSubreportTotalcontrol],0)

control source for second subreport...
=IIf([YourSecondSubreportName].[Report].[HasData]=-1,[YourSecondSubreportName].[Report]![YourFirstSubreportTotalcontrol],0)

and so on...

I assume that you have a control that pulls the total in each subreport. That is the, for example, "YourFirstSubreportTotalcontrol" reference.

Hope that helps.

Tom

 

[floydpepper]

Tom,
You are the man! Thank you. Here's a star brother.

Floyd

 

[THWatson]

Glad to help. Thanks for the star.
Tom

 

[xicana]

Tom,

This posting was very helpful to me as well - but I'm running into a slight problem.

I have One Main report and one subreport.
They are linked by JobNumber.
The main report is grouped by job number.

The subreport pulls up the different invoices for the job in Main report. The actual subreport is hidden(i.e not visible) - but I want to pull a calculated field from the subreport into the group header for each job number.

I have my subreport in the detail section of the report and I have an unbound text box in the job# group header that has this as it's control source:

=IIf([InvoiceTotals subreport].Report.HasData=-1,[InvoiceTotals subreport].Report!txtInvoiceTotal,0)

It's pulling up the totals but it's putting them together with the wrong job number for ex:
My report should look like this:
job1 0
job2 250
job3 500

But its displaying like this:
job1 500
job2 0
job3 250

Am I putting the subreport in the wrong section? I don't understand if the calculated fields are getting pulled up - why is it not matching up with the job number?

Sandy

Sandy

 

[xicana]

Hmmm - I found the problem...after thinking about this for a minute I realized that it was probably looking at the information that the subreport had pulled up for the previous job number and was testing the "hasData" property on that value.

Anyway,

Instead of referencing the calculated control from my subreport in the group header - I referenced it in the group footer and that seemed to solve my problem. This is going to mean some serious reformatting on this complicated report (this is only a piece of it)...is there anyway to get the right data on the group header?

Sandy

Sandy