trouble with SUM function

trouble with SUM function - Repost

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Slez - 29 Dec 2006 16:39 GMT

OOPS!...Hit something as I was typing that made it post...Here is the
"complete" post!

I am not getting a control on a report to sum properly.
Groups are as follows:
ProjectName
BidNumber
Detail Section

The BidNumber footer has a control named "BidTotal" in which the Control
Source is:
=Sum([LineTotalSellPrice])+[SalesTaxAmount]+[UseTaxAmount]
This works perfectly!
LineTotalSellPrice exists in the detail section and the other 2 exist in the
BidNumber header.

I'd like the sum of what shows up in the BidTotal control per ProjectName, so
I added a control in the ProjectName footer and tried the following 4
expresiions in the Control Source:
=Sum(BidTotal)
=Sum([BidTotal])
=Sum([LineTotalSellPrice])+[SalesTaxAmount]+[UseTaxAmount]
=Sum([LineTotalSellPrice])+Sum([SalesTaxAmount])+Sum([UseTaxAmount])

All of these have failed to work. In the first 2 cases, it prompts me for
BidTotal when I run the report.
What am I doing wrong?
Any help is appreciated!
Slez

Reply to this Message

Marshall Barton - 29 Dec 2006 18:37 GMT

>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

Signature

Marsh
MVP [MS Access]

Reply to this Message

Slez - 02 Jan 2007 16:52 GMT

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?

Thanks again!
Slez

>>OOPS!...Hit something as I was typing that made it post...Here is the
>>"complete" post!
[quoted text clipped - 17 lines]
>expression like:
>=Sum([LineTotalSellPrice])+txtRunSalesTaxAmount+txtRunUseTaxAmount

Reply to this Message

Marshall Barton - 02 Jan 2007 18:36 GMT

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.

Signature

Marsh
MVP [MS Access]

>Thanks for your reply Marshall! I was able to get it to work in the
>ProjectName Footer with the following Control Source: =Sum(
[quoted text clipped - 36 lines]
>>expression like:
>>=Sum([LineTotalSellPrice])+txtRunSalesTaxAmount+txtRunUseTaxAmount

Reply to this Message