Sum of Sum in Totals Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joost - 15 Dec 2005 03:30 GMT

I have a form based on a totals query, with one of the fields as the
sum of the quantity of an article of the grouped records. I get an
#error# in a control on the form where I want to display the sum of
this sum. TxtExample =sum(SumOfQuantity).
I understand that I can't sum a calculated control but I would argue
this is not a calculated control. I have been wrong before though....

Any help would be very much apprciated, thanks in advance

Reply to this Message

Allen Browne - 15 Dec 2005 06:51 GMT

You can sum the calculated query field.

From your description, it sounds like your text box has these properties:
Name txtExample
Control Source =Sum([SumOfQuantity])
Set its Format property to Currency or General Number so Access understands
the type.

If that still fails, temporarily try:
=[SumOfQuantity]
just to check that everything else is in order.

You might also typecast the expression in your query.
Switch the query to SQL View (View menu.)
Where you see:
Sum([Quantity]) AS SumOfQuantity
change it to:
CCur(Nz(Sum([Quantity]),0)) AS SumOfQuantity
For an explanation, see:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

Please post a reply if you are still stuck, as your approach should work.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>I have a form based on a totals query, with one of the fields as the
> sum of the quantity of an article of the grouped records. I get an
[quoted text clipped - 4 lines]
>
> Any help would be very much apprciated, thanks in advance

Reply to this Message

Joost - 16 Dec 2005 16:35 GMT

Thanks for taking the time Allen,

I set the Format properties; this didn't make any difference. I put the
control to =[SumOfQuantity] and that worked OK.
As you told me the approach should work, I created a new form based on
the query, and put a Control to =Sum([SumOfQuantity]) That worked
perfectly fine on this form....

So I went back to my original form and played around a little bit. One
thing I did was to change the underlying query and instead of SELECT
[QryName].* ,select all the underlying Fields of the Query
individually, among others the one the Control gave me the error on:
[QryName].SumOfCantidad.
That didn't make any difference either, but when I changed he name of
that field to an expression with the same name ("SumOfQuantity AS
SumOfQuantity") believe it or not, it worked, and from that moment on
everything worked perfectly, even going back to the situation that gave
me the trouble and made me post the message.
I know it sounds weird, but it's like Access understood what I wanted
from that moment on....it's like beyond the stage of "Artificial"
Intelligence......

Any explanation?? If not once again thanks for your insight. I have
read dozens of your contributions and they have helped me enormously...

Reply to this Message

Allen Browne - 16 Dec 2005 17:21 GMT

Have you unchecked the boxes under:
Tools | Options | General | Name AutoCorrect
If not, you *really* need to do that. There are literally dozens of problems
with misassigned names that trace back to this misfeature. Here's just a
few:
http://allenbrowne.com/bug-03.html

Even with that Name AutoCorrupt disabled, I think I saw this issue once,
where changing the field from the default name solved the problem. I can't
remember, but I suspect there ended up being something else that was wrong
with that query as well, and so the change of name was really just masking
the true source of the problem.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks for taking the time Allen,
>
[quoted text clipped - 20 lines]
> Any explanation?? If not once again thanks for your insight. I have
> read dozens of your contributions and they have helped me enormously...

Reply to this Message