Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 27th, 2005, 03:33 PM
Authorized User
 
Join Date: Jun 2003
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default No data in subreport error

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
 
Old December 27th, 2005, 04:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old December 27th, 2005, 05:30 PM
Authorized User
 
Join Date: Jun 2003
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 27th, 2005, 05:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old December 27th, 2005, 07:18 PM
Authorized User
 
Join Date: Jun 2003
Posts: 34
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 28th, 2005, 08:56 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old December 28th, 2005, 08:58 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Display subreport with no underlying data bph Access 4 April 11th, 2005 04:08 PM
Subreport not displaying no data message in main lic023 Access 0 March 15th, 2005 08:15 AM
DOS Error when reimport subreport arthurs Crystal Reports 0 January 27th, 2005 08:19 AM
Can Main and SubReport get data fr different table yblee Crystal Reports 0 August 19th, 2004 03:37 AM
SubReport in data report vb6? stupidz VB Databases Basics 0 March 1st, 2004 04:18 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.