Hello everyone,

I have an adp (2003) where I have a report with subreports and formulas in the main report based on fields in the subreports. as you can imagine, when one of the subreports does not have any values it does now display and the formulas display "ERROR".

I tried "isnull" and "nz" but I get an error stating that I referenced an object with no value. I also tried feeding a static "0" (zero character) in the field under the no data event but it says that I can not change the control source after the report has opened.

Any ideas?????

__________________
Thanks,
Jesse

Where are you using the IsNull or Nz functions? Are you using them in the report's detail section or the header section? Where on the subform is the value of the item you're passing? Is THAT on the subform's header section or detail section? I ask because the way Access initializes variables matters depending on which section of the report you're in.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Thanks for your quick reply Greg,

To give you a better I idea of what I'm doing;
I have a formula in the main report's footer that adds summaries (sum(field1))from the footer section of each of the subreports.

Luckly I found a solution by combining an "IIF" and "hasdata" in the formaula and it works great! here is an example of what I did.

Original:
[Reports]![Main_Report]![Sub_Report1].[field1] + [Reports]![Main_Report]![Sub_Report2].[field1]

New:
iif([sub_Report1].[report].hasdata=true,[Reports]![Main_Report]![Sub_Report1].[field1] + [Reports]![Main_Report]![Sub_Report2].[field1],[Reports]![Main_Report]![Sub_Report2].[field1])

This now return the value of the sub_report2 summary when there is no data for sub_report1 instead of "#ERROR".

This is assuming that subreport has data sometimes and sub_report2 allways has data as in my case, otherwise you would test both with "hasdata=true" instead of just sub_report1.

Hope that this helps someone else.

Thanks again for your reposnse Greg.

Thanks,
Jesse

I'm glad you found an answer. I also suggest you look at the functions IsNull() and Nz().

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I did attempt to use both of those but the problem is that any formula that references a field in a subreport that does not contain any data will result in an error. Both of those options check for null or zero length values, however with no data there is no value hense the dreaded "#ERROR"!

Thanks,
Jesse

There's a difference between a value that has no error and one that is not initilized yet.

Hence, if you use any functions or formulas in the report's header section that use a value from the detail section, you will error out because the objects in the detail section have not been initialized yet let alone have a null value. After the entire report loads, even though there is no record at all, the IsNull and Nz functions should work as intended. I think the problem is that they're being used somewhere when the report has not finished loading yet. I'd have to see your report and the sequence of events.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I meant to say in my first sentence, there's a difference between a control that has no value and one that has not been initialized yet.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division