Dave:
As you want this in the page footer you have to do it in code, you can't
call the Sum function in the control's ControlSource property. I'm
assuming
that a new page is forced after each invoice; otherwise if a new invoice
started part way down the page the value in the footer would relate to
the
second invoice. On that assumption:
1. Leave the ControlSource property of the purple control in the page
footer blank. Lets assume this control is called txtInvoiceTotal.
2. In the Print event procedure of the InvoiceNumber Header section
initialise the txtInvoiceTotal control to zero:
txtInvoiceTotal = 0
3. In the Material Footer section's print event procedure increment the
txtInvoiceTotal control with thee value of the green control, which I'll
assume is called txtSubTotal:
If PrintCount = 1 Then
txtInvoiceTotal = txtInvoiceTotal + txtSubTotal
End if
If you find you are getting slight discrepancies between subtotals and
totals its important to realize that the Currency data type has a
precision
of four decimal places. This is to remove cumulative rounding errors, so
can
mean that a grand total of values resulting from computations such as the
application of a tax rate or discount rate might differ slightly from the
perceived sum of a number of subtotals. This is of course because the
subtotals are rounded to two decimal places but the grand total is that
of
the underlying more precise values.
Ken Sheridan
Stafford, England
:
Here is a picture of the report I am working on.
http://www.almarsupplies.com/invoice.jpg
The purple field is summing all the weight of the red field. The green
field is multiplying the summed weight times the price in the yellow
field.
I need to now sum the green field and put the value in the blue field.
I did make a field above the red field that would sum the weight * price
and
that seems to work well, but it doesn't take into account price errors
or
discrepencies. I would like to be able to sum the green field and
compare
it to my other summed field to check for errors. Does anyone know how I
can
sum the green field? There could be many of these forms per invoice.
Thanks,
Dave
Dave:
You can't use an aggregate function like Sum in a page footer, only in
a
group or report footer. To sum all the subtotals for the whole report
on
each page you can put a hidden text box in the report footer with
exactly
the
same ControlSource as the subtotalcalc text box, i.e. you sum all the
individual values not the subtotals; the result is the same of course.
Then
put a text box in the page footer which references the hidden control
in
the
report footer, e.g.
=[grandtotalcalc])
If you want incrementing cumulative totals in the page footer, i.e.
the
total of all subtotals up to the last one on the current page then put
a
text
box in the page footer, txtCumulativeTotal say. In the report header's
print
event procedure initialise the text box to zero:
Me.txtCumulativeTotal = 0
In the group footer's Print event procedure increment the text box,
examining the PrintCount property to prevent any inadvertent double
counting:
If PrintCount = 1 Then
Me.txtCumulativeTotal = Me.txtCumulativeTotal + Me.subtotalcalc
End If
If you want to sum the subtotals for the current page you do the same,
but
initialise the text box to zero in the page header's print event
procedure.
Note that when using the Print event like this, if you jump over pages
in
a
report in print preview using the navigation bar, the skipped pages'
Print
events don't fire so the cumulative total will be wrong. Paging
through
the
pages sequentially or sending the whole report to the printer is fine
though.
It is possible to avoid this limitation by using the Format event
procedure
to increment the control, but the procedure can fire multiple times
and
have
a FormatCount value of 1 each time, thus double counting. This
requires
the
surplus increment to be undone in the Retreat event procedure. It can
be
tricky to control therefore, whereas using the Print event procedure
is
easier to predict.
Ken Sheridan
Stafford, England
:
I have a report that has subtotals from the detail section in a group
footer. I would like to sum all of the subtotals from the group
footer
in
the page footer. The subtotal field to be summed is subtotalcalc.
In
the
page footer I made an unbound field and set it equal to
=Sum([subtotalcalc]). I set the field to both sum over group and
over
all.
Neither seems to be working. How can I get the sum of this field?
Thanks,
Dave
