Here is a scenario from my report:
The report will grab data with a main query into a parent row
Depending on the data, a child row may exist, if it does the report will
display it
The child row will use data from the parent row in order to do a calculation
in the child

All of this works just fine and the report data is correct.

Now, what I am having issues with is doing the page/report sum calculations.
The requirements dictate that I need to create a sum of both the parent
column and also the child column in the totals.

I have been trying to do this all day and cannot seem to get it to work at
all.

In the page totals, I am trying get something like this:
=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )

But, when I try to execute this, it prompts me for the [PegStdLabor] value
because it is being referenced but the sub-report does not exist yet.

I have also tried the HasData Report parameter (similar problem), and
IsObject, IsError, IsMissing, etc...

I am confused to no end. Any help is appreciated.

The title has been corrected. I apologize for any initial confusion

"rickr" wrote:

> Here is a scenario from my report:
> The report will grab data with a main query into a parent row
> Depending on the data, a child row may exist, if it does the report will
> display it
> The child row will use data from the parent row in order to do a calculation
> in the child
>
> All of this works just fine and the report data is correct.
>
> Now, what I am having issues with is doing the page/report sum calculations.
> The requirements dictate that I need to create a sum of both the parent
> column and also the child column in the totals.
>
> I have been trying to do this all day and cannot seem to get it to work at
> all.
>
> In the page totals, I am trying get something like this:
> =Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )
>
> But, when I try to execute this, it prompts me for the [PegStdLabor] value
> because it is being referenced but the sub-report does not exist yet.
>
> I have also tried the HasData Report parameter (similar problem), and
> IsObject, IsError, IsMissing, etc...
>
> I am confused to no end. Any help is appreciated.

Unfortunately, that does not work. When I run the report it prompts me for the
Product - Pegged]Report.HasData and also for the [Product -
Pegged].Report![PegStdLabor] value.

This is the thing I have been fighting for 2 days now.

"Marshall Barton" wrote:

> rickr wrote:
>
> >Here is a scenario from my report:
> >The report will grab data with a main query into a parent row
> >Depending on the data, a child row may exist, if it does the report will
> >display it
> >The child row will use data from the parent row in order to do a calculation
> >in the child
> >
> >All of this works just fine and the report data is correct.
> >
> >Now, what I am having issues with is doing the page/report sum calculations.
> >The requirements dictate that I need to create a sum of both the parent
> >column and also the child column in the totals.
> >
> >I have been trying to do this all day and cannot seem to get it to work at
> >all.
> >
> >In the page totals, I am trying get something like this:
> >=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )
> >
> >But, when I try to execute this, it prompts me for the [PegStdLabor] value
> >because it is being referenced but the sub-report does not exist yet.
> >
> >I have also tried the HasData Report parameter (similar problem), and
> >IsObject, IsError, IsMissing, etc...
>
>
> If the subreport might not have any records, change the
> expression to:
>
> =Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
> [Product - Pegged].Report![PegStdLabor], 0))
>
> --
> Marsh
> MVP [MS Access]
> .
>

I am playing with VB code builder and I am able to get the sum of these
fields into a variable, but the code builder will not allow me to assign it
to a field on the report.

What is the syntax to assign a Code Builder variable to a report field?

"rickr" wrote:

> Unfortunately, that does not work. When I run the report it prompts me for the
> Product - Pegged]Report.HasData and also for the [Product -
> Pegged].Report![PegStdLabor] value.
>
> This is the thing I have been fighting for 2 days now.
>
> "Marshall Barton" wrote:
>
> > rickr wrote:
> >
> > >Here is a scenario from my report:
> > >The report will grab data with a main query into a parent row
> > >Depending on the data, a child row may exist, if it does the report will
> > >display it
> > >The child row will use data from the parent row in order to do a calculation
> > >in the child
> > >
> > >All of this works just fine and the report data is correct.
> > >
> > >Now, what I am having issues with is doing the page/report sum calculations.
> > >The requirements dictate that I need to create a sum of both the parent
> > >column and also the child column in the totals.
> > >
> > >I have been trying to do this all day and cannot seem to get it to work at
> > >all.
> > >
> > >In the page totals, I am trying get something like this:
> > >=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )
> > >
> > >But, when I try to execute this, it prompts me for the [PegStdLabor] value
> > >because it is being referenced but the sub-report does not exist yet.
> > >
> > >I have also tried the HasData Report parameter (similar problem), and
> > >IsObject, IsError, IsMissing, etc...
> >
> >
> > If the subreport might not have any records, change the
> > expression to:
> >
> > =Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
> > [Product - Pegged].Report![PegStdLabor], 0))
> >
> > --
> > Marsh
> > MVP [MS Access]
> > .
> >

Here is the VB Code I am using to calculate the sums of the stdLabor fields.
It is summing the fields correctly, but when I try to assign it to the Report
Footer object (in this case Text71) I get the error message stating that I
cannot assign a value to this object (run-time error '2448').

CODE
Option Compare Database
Dim stdLaborSum As Double

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
stdLaborSum = stdLaborSum + StdLabor + IIf([Report_Product -
Pegged].HasData, [Report_Product - Pegged].Text14, 0)
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'Reports![Prod - Left to Manufacture - By Plant pegged]![Text71] = stdLaborSum
'Reports![Prod - Left to Manufacture - By Plant pegged] = stdLaborSum
[Report_Prod - Left to Manufacture - By Plant pegged].Controls!Text71 =
stdLaborSum
'Me![Text71] = stdLaborSum
'Fields("Text71").Value = stdLaborSum
End Sub

END CODE

Any help is appreciated.

Here is the VB Code I am using to calculate the sums of the stdLabor fields.
It is summing the fields correctly, but when I try to assign it to the Report
Footer object (in this case Text71) I get the error message stating that I
cannot assign a value to this object (run-time error '2448').
CODE
Option Compare Database
Dim stdLaborSum As Double

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
stdLaborSum = stdLaborSum + StdLabor + IIf([Report_Product -
Pegged].HasData, [Report_Product - Pegged].Text14, 0)
End Sub

Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
'Reports![Prod - Left to Manufacture - By Plant pegged]![Text71] = stdLaborSum
'Reports![Prod - Left to Manufacture - By Plant pegged] = stdLaborSum
[Report_Prod - Left to Manufacture - By Plant pegged].Controls!Text71 =
stdLaborSum
'Me![Text71] = stdLaborSum
'Fields("Text71").Value = stdLaborSum
End Sub


Any help is appreciated.

"rickr" wrote:

> I am playing with VB code builder and I am able to get the sum of these
> fields into a variable, but the code builder will not allow me to assign it
> to a field on the report.
>
> What is the syntax to assign a Code Builder variable to a report field?
>
> "rickr" wrote:
>
> > Unfortunately, that does not work. When I run the report it prompts me for the
> > Product - Pegged]Report.HasData and also for the [Product -
> > Pegged].Report![PegStdLabor] value.
> >
> > This is the thing I have been fighting for 2 days now.
> >
> > "Marshall Barton" wrote:
> >
> > > rickr wrote:
> > >
> > > >Here is a scenario from my report:
> > > >The report will grab data with a main query into a parent row
> > > >Depending on the data, a child row may exist, if it does the report will
> > > >display it
> > > >The child row will use data from the parent row in order to do a calculation
> > > >in the child
> > > >
> > > >All of this works just fine and the report data is correct.
> > > >
> > > >Now, what I am having issues with is doing the page/report sum calculations.
> > > >The requirements dictate that I need to create a sum of both the parent
> > > >column and also the child column in the totals.
> > > >
> > > >I have been trying to do this all day and cannot seem to get it to work at
> > > >all.
> > > >
> > > >In the page totals, I am trying get something like this:
> > > >=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )
> > > >
> > > >But, when I try to execute this, it prompts me for the [PegStdLabor] value
> > > >because it is being referenced but the sub-report does not exist yet.
> > > >
> > > >I have also tried the HasData Report parameter (similar problem), and
> > > >IsObject, IsError, IsMissing, etc...
> > >
> > >
> > > If the subreport might not have any records, change the
> > > expression to:
> > >
> > > =Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
> > > [Product - Pegged].Report![PegStdLabor], 0))
> > >
> > > --
> > > Marsh
> > > MVP [MS Access]
> > > .
> > >

That definitely did the trick. Thank you ever so much.

Sometimes it is the simple things that really cause the most difficulties. I
truly appreciate your help.

So, for those that may want a solution recap:
If you have a report with a sub report, and need to use values from the
sub-report in order to calculate total report values, follow this procedure:
1. For each sub-report value that needs to be referenced on the main report
totals
a. Create a text-box and place it on the main detail section, name it
something useful like 'col_1_Sum'
b. In the Control Source, reference the sub-report data element like so:

=IIf([Sub-Report_Name].Report.HasData,Nz([Sub-Report_Name].Report.column1,0),0)
c. Set the text-box 'Running Sum' property as necessary (I created 2
text boxes, 1 for the primary group and the second for the 'Over All' option)
d. Optional: Make the text-box NOT Visible
2. In the Report Footer, set the text-box where the total is going to be
displayed like this (using the text box with Over All running sum):
= Sum([col1]) + [col_1_sum]

Works like a charm.

Thanks again.

"Marshall Barton" wrote:

> Sorry, I was so focused on the subreport reference that I
> missed the thee fact that you were using Sum to try to total
> the subreport values. That won't work because the aggregate
> functions (Count, Sum, etc) can only operate on revord
> source fields, they are unaware of controls in the report.
>
> Instead you should use a running sum text box (named
> txtRunTotal) in the same section as the subreport. The text
> box's expression would be like:
> =IIf([Product - Pegged].Report.HasData, [Product -
> Pegged].Report![PegStdLabor], 0)
>
> Then the report footer textbox can use the expression:
> =Sum([StdLabor]) + txtRunTotal
> --
> Marsh
> MVP [MS Access]
>
>
> rickr wrote:
> >Unfortunately, that does not work. When I run the report it prompts me for the
> >Product - Pegged]Report.HasData and also for the [Product -
> >Pegged].Report![PegStdLabor] value.
> >
> >This is the thing I have been fighting for 2 days now.
> >
> >"Marshall Barton" wrote:
> >> rickr wrote:
> >> >Here is a scenario from my report:
> >> >The report will grab data with a main query into a parent row
> >> >Depending on the data, a child row may exist, if it does the report will
> >> >display it
> >> >The child row will use data from the parent row in order to do a calculation
> >> >in the child
> >> >
> >> >All of this works just fine and the report data is correct.
> >> >
> >> >Now, what I am having issues with is doing the page/report sum calculations.
> >> >The requirements dictate that I need to create a sum of both the parent
> >> >column and also the child column in the totals.
> >> >
> >> >I have been trying to do this all day and cannot seem to get it to work at
> >> >all.
> >> >
> >> >In the page totals, I am trying get something like this:
> >> >=Sum([StdLabor])+sum( [Product - Pegged].Report![PegStdLabor] )
> >> >
> >> >But, when I try to execute this, it prompts me for the [PegStdLabor] value
> >> >because it is being referenced but the sub-report does not exist yet.
> >> >
> >> >I have also tried the HasData Report parameter (similar problem), and
> >> >IsObject, IsError, IsMissing, etc...
> >>
> >>
> >> If the subreport might not have any records, change the
> >> expression to:
> >>
> >> =Sum(StdLabor)+Sum(IIf([Product - Pegged].Report.HasData,
> >> [Product - Pegged].Report![PegStdLabor], 0))
> .
>