Re: trouble with SUM function - Repost

The problem is that when you reference a value before it has
been calculated, you can get all kinds of strange values.
Depending on the version of Access, it might be the value
from a previous group or the value in the first record in
the current group or ???.

Getting the footer value to display correctly in the header
is a somewhat convoluted exercise that should be avoided if
you can live with it in the footer, or alternatively do the
calculations in the record source query.

Sorting the report by a calculated value requires the
calculation to be done in the report's record source query.
This is usually not particularly difficult if you use a
Totals query to calculate the group total and then Join the
query to the table/query you are using now. Combining the
need to sort and the desire to display the total in the
group header, I think using the query approach is clearly
the way to go.
--
Marsh
MVP [MS Access]


Slez via AccessMonster.com wrote:
Thanks for your reply Marshall! I was able to get it to work in the
ProjectName Footer with the following Control Source: =Sum(
[LineTotalSellPrice])+[txtRunUseTax]+[txtRunSalesTax]
I named this control txtProjectTotal.

In the interest of compacting the report a bit, my hope was to have this
total show in the ProjectName Header, so I added a control in that and
entered the following Control Source: =txtProjectTotal

Doing that not only gave me an incorrect total in that added control, but the
control in the footer actually changed to the incorrect value also.
Incidently, it only added the sum of the "Tax" controls from the first record
in the BidNumber group. Seems strange, but I'm wondering if you have any
thoughts as to why that would occur. I can certainly live with the total in
the footer.

Also, do you know if it's possible to sort the report in descending order by
the values it generates in the control txtProjectTotal?


Marshall Barton wrote:
OOPS!...Hit something as I was typing that made it post...Here is the
"complete" post!
[quoted text clipped - 22 lines]
All of these have failed to work. In the first 2 cases, it prompts me for
BidTotal when I run the report.

The aggregate functions only operate on **fields** in the
report's record source table/query. They are unaware of
**controls** in the report.

To total a control (named txtA) in a group header/footer,
you can add a text box (named txtRunA) next to txtA. Set
txtRunA control source property to =txtA and its RunningSum
property to Over Group.

Then the next higher leverl group footer can display the
total in a text box with the expression =txtRunA

In your case a ProjectNameTotal text box would have an
expression like:
=Sum([LineTotalSellPrice])+txtRunSalesTaxAmount+txtRunUseTaxAmount


.

Relevant Pages

  • Re: Two different totals from one field depending on criteria
    ... It is not unusual for one bad aggregate expression to mess ... the foirm's record source is a query that somehow converted ... Copy/Paste of the week1 text box control source expression ... Now, none of my totals ...
    (microsoft.public.access.formscoding)
  • Re: Two different totals from one field depending on criteria
    ... FROM Home INNER JOIN (Employee INNER JOIN (INNER JOIN Calendar ON ... the foirm's record source is a query that somehow converted ... Copy/Paste of the week1 text box control source expression ... Now, none of my totals ...
    (microsoft.public.access.formscoding)
  • Re: Two different totals from one field depending on criteria
    ... table/query and a control on a form/report. ... fields you mean when you say they must be in the form's record source table ... user would like to have separate totals by week (not a separate schedule, ... You can use this kind of expression in a form/report ...
    (microsoft.public.access.formscoding)
  • Re: Two different totals from one field depending on criteria
    ... I think I get it now as far as the field versus the control. ... Now, none of my totals ... fields you mean when you say they must be in the form's record source table ... You can use this kind of expression in a form/report ...
    (microsoft.public.access.formscoding)
  • Re: Update with calculation
    ... Calculated data. ... Just because one control can have only ONE record source. ... And if your control is calculated, the Record Source ... You have 5000 records, and when you prefrorm calculations, the data is ...
    (microsoft.public.access.tablesdbdesign)