By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,831 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,831 IT Pros & Developers. It's quick & easy.

Retrieve fields on report from two subreports

100+
P: 294
I am trying to pull the SUM() from 2 subreports. It works for one subreport, however not for the other.

What I tried: I Sum() the field in the subreports, so I can refer to that field on the main report. However, for the test case I am trying, there are no records for one of the subreports, so I tried:

Expand|Select|Wrap|Line Numbers
  1. =IIf([PayoutByYrLTTSbRpt].[Report].[NoRecords],0,[PayoutByYrLTTSbRpt].[Report].[LTtotal])
Expand|Select|Wrap|Line Numbers
  1. =IIf([PayoutByYrLTTSbRpt].[Report].[NoData],0,[PayoutByYrLTTSbRpt].[Report].[LTtotal])

And I also tried :

Expand|Select|Wrap|Line Numbers
  1. =IIf([PayoutByYrLTTSbRpt].[Report].[HasData], [PayoutByYrLTTSbRpt].[Report].[LTtotal],0)
Both give me the `#Name?` error when ran. I've tested `=[PayoutByYrLTTSbRpt].[Report].[PytGross]` and it doesn't throw an error in design view, but when ran it does. That tells me it is an issue at run time because the report was able to find the fields I am referring to in design view (thus naming is correct).

For the subreport that is giving me trouble, I have :

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3.     Private Sub Report_NoData(Cancel As Integer)
  4.     Cancel = True
  5.     End Sub
  6.  
><
May 1 '14 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,327
because the report was able to find the fields I am referring to in design view (thus naming is correct) (...) and it doesn't throw an error in design view (...)
#Name? Errors only show at run time in the report. Therefor, your logic does not hold to be true.

Both give me the `#Name?` error when ran. I've tested `=[PayoutByYrLTTSbRpt].[Report].[PytGross]
This indicates that the field/control you are refering to does not exsist and this usually do to either a typo or incorrect referenceing.
Now because I'm not exactly sure of where the [PytGross] is located on your form, it's hard to determine what reference you need to make.
For example if you have a OutsideForm, InsideLeft, InsideRight
and for the InsideLeft![textbox1] and for the InsideRight[Textbox2] and InsideRight[Textbox3]
If you wanted the sum of Textbox2 and Textbox3 so show in Textbox1 then in the InsideLeft![textbox1] - in the control source you would need:
Expand|Select|Wrap|Line Numbers
  1. =CLng([Forms]![Outside]![InsideRight]![text2])+CLng([Forms]![Outside]![InsideRight]![text3])
For formatting purposes (I don't want a block of white space where that subreport should have been, this is necessary for each subreport).
One question per thread please
May 1 '14 #2

100+
P: 294
I'm sorry you interpreted my last sentence as another question - I was stating that the code segment
Expand|Select|Wrap|Line Numbers
  1. Cancel = True
, had it been related to my problem, is necessary.

As far as the meat of your response - The two subreports are set up the exact same way. The field I am summing (PytGross) is a field in the detail section of each subreport. I'm not sure what you mean by InsideLeft InsideRight. PytGross is not a field on a form, but rather a field in the query and on the report.

That being said, I am referencing both fields on each subreport the exact same way, which is why I thought it might have been related to the Cancel = True bit of code on the report where I am not getting any data.

I don't understand why
Expand|Select|Wrap|Line Numbers
  1. =[PayoutByYrLTRSbRpt].[Report].[LRtotal]
works, but
Expand|Select|Wrap|Line Numbers
  1. =[PayoutByYrLTTSbRpt].[Report].[LTtotal]
doesn't.

LTtotal is the field that isn't working, specifically, when being called. It is a textbox with this bit of code in it
Expand|Select|Wrap|Line Numbers
  1. =Sum([PytGross])
as the control source.
[PytGross] is a textbox with name PytGross_txt and Control Source PytGross.

Should I be referencing PytGross_txt or PytGross in the bit of
Expand|Select|Wrap|Line Numbers
  1. =Sum([PytGross])
? Maybe that's where the error lies.
May 1 '14 #3

zmbd
Expert Mod 5K+
P: 5,327
For example if you have a OutsideForm, InsideLeft, InsideRight
and for the InsideLeft![textbox1] and for the InsideRight[Textbox2] and InsideRight[Textbox3]
If you wanted the sum of Textbox2 and Textbox3 so show in Textbox1 then in the InsideLeft![textbox1] - in the control source you would need:
Three forms
Outside
then subform on the left of hte parent outside form
another subform on the rightside of the parent outside form
...
May 1 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.