summing up values of a subform on the main form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a main form Invoices with a subform holding invoice details. On the
subform there are calculated fielsd NetValue (=[Quantity]*[Price]), VATValue
(=[NetValue]*[VATPerc]/100), and GrossValue (=[NetValue]+[VATValue]. There
is no problem with that. On the main form I have to show the sum of the
calculated values. I created unbound boxes in the foot section of the
subform. When I create the first one =Sum([Quantity]*[Price]) there is no
problem. When I create the
second =Sum([NetValue]+[VATValue]) or the third one
=Sum([NetValue]+[VATValue]), all boxes show #Error even the one which worked
fine up till then. Can there be just one summed up calculated field in a foot
section?
 
Coldeyes said:
I have a main form Invoices with a subform holding invoice details. On the
subform there are calculated fielsd NetValue (=[Quantity]*[Price]), VATValue
(=[NetValue]*[VATPerc]/100), and GrossValue (=[NetValue]+[VATValue]. There
is no problem with that. On the main form I have to show the sum of the
calculated values. I created unbound boxes in the foot section of the
subform. When I create the first one =Sum([Quantity]*[Price]) there is no
problem. When I create the
second =Sum([NetValue]+[VATValue]) or the third one
=Sum([NetValue]+[VATValue]), all boxes show #Error even the one which worked
fine up till then. Can there be just one summed up calculated field in a foot
section?
Click to expand...


THe aggregate functions only operate on fields in the record
source tab;e/query, they are not aware of controls on a
form/report.

Change your expressions to use only the fields:

=Sum(([Quantity]*[Price])*[VATPerc]/100)

=Sum(([Quantity]*[Price])*(1+[VATPerc]/100))
 
Thank you very much Marshall. Is there an explanation for why the first box
which worked fine gives the error message too when creating the second -
incorrect - field?

Marshall Barton said:
Coldeyes said:
I have a main form Invoices with a subform holding invoice details. On the
subform there are calculated fielsd NetValue (=[Quantity]*[Price]), VATValue
(=[NetValue]*[VATPerc]/100), and GrossValue (=[NetValue]+[VATValue]. There
is no problem with that. On the main form I have to show the sum of the
calculated values. I created unbound boxes in the foot section of the
subform. When I create the first one =Sum([Quantity]*[Price]) there is no
problem. When I create the
second =Sum([NetValue]+[VATValue]) or the third one
=Sum([NetValue]+[VATValue]), all boxes show #Error even the one which worked
fine up till then. Can there be just one summed up calculated field in a foot
section?
Click to expand...


THe aggregate functions only operate on fields in the record
source tab;e/query, they are not aware of controls on a
form/report.

Change your expressions to use only the fields:

=Sum(([Quantity]*[Price])*[VATPerc]/100)

=Sum(([Quantity]*[Price])*(1+[VATPerc]/100))
Click to expand...
 
No, no real explanation for that, but it is common for an
error in one aggregate function to mess over the others.

I'll guess that Access creates a temporary query to
calculate the aggregate values and when the query has an
error, it can't calculate any values.
--
Marsh
MVP [MS Access]


Thank you very much Marshall. Is there an explanation for why the first box
which worked fine gives the error message too when creating the second -
incorrect - field?

Coldeyes said:
I have a main form Invoices with a subform holding invoice details. On the
subform there are calculated fielsd NetValue (=[Quantity]*[Price]), VATValue
(=[NetValue]*[VATPerc]/100), and GrossValue (=[NetValue]+[VATValue]. There
is no problem with that. On the main form I have to show the sum of the
calculated values. I created unbound boxes in the foot section of the
subform. When I create the first one =Sum([Quantity]*[Price]) there is no
problem. When I create the
second =Sum([NetValue]+[VATValue]) or the third one
=Sum([NetValue]+[VATValue]), all boxes show #Error even the one which worked
fine up till then. Can there be just one summed up calculated field in a foot
section?
Click to expand...
Click to expand...
Marshall Barton said:
THe aggregate functions only operate on fields in the record
source tab;e/query, they are not aware of controls on a
form/report.

Change your expressions to use only the fields:

=Sum(([Quantity]*[Price])*[VATPerc]/100)

=Sum(([Quantity]*[Price])*(1+[VATPerc]/100))
Click to expand...
Click to expand...
 
Back
Top