Sum of Sum in Totals Query

[Joost]

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(SumOfQuant ity).
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

[Allen Browne]

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.

--
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.

"Joost" <am**********@y ahoo.com> wrote in message
news:11******** *************@g 43g2000cwa.goog legroups.com...

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(SumOfQuant ity).
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

[Joost]

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 ("SumOfQuant ity 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...

[Allen Browne]

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.

--
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.

"Joost" <am**********@y ahoo.com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

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 ("SumOfQuant ity 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...