Prev: Printing only fields that contain data
Next: Report title based on Date?
From: rickr on 22 Apr 2010 08:19
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]
> .
>
From: rickr on 22 Apr 2010 08:35
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]
> > .
> >
From: rickr on 22 Apr 2010 10:41
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.


From: Marshall Barton on 22 Apr 2010 12:24
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))
From: Marshall Barton on 22 Apr 2010 12:33
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?


Stop right there. Step away from the keybord and keep your
hands up ;-)

Because report sections are processed as many times and in
whatever order is necessary for Access to produce the
formatting and display you want, you can not use code to
calculate values from more than the one section with the
code.

See my earlier reply about using a Running Sum text box
instead of the doomed idea of using code to calculate a
total.

--
Marsh
MVP [MS Access]
 |  Next  |  Last
Pages: 1 2
Prev: Printing only fields that contain data
Next: Report title based on Date?