How to sum up textboxs on a report?

[kai]

Hi, All
I use Access2002. I have a report, on each group I have a subtotal (not
directly from fields, get from on report calculation) with name txtSubTotal.
At report level, I want sum up "txtSubTotal", I use Sum([txtSubTotal]) but
Access pops up a input window asking for parameter for "txtSubTotal". Is it
possible to sum up textbox on a report? I tried everything, I still could
find the solution. I really appreciated any help.

Thanks

Kai

 

[Marshall Barton]

I use Access2002. I have a report, on each group I have a subtotal (not
directly from fields, get from on report calculation) with name txtSubTotal.
At report level, I want sum up "txtSubTotal", I use Sum([txtSubTotal]) but
Access pops up a input window asking for parameter for "txtSubTotal". Is it
possible to sum up textbox on a report?

The aggregate functions work with fields in the report's
record source table/query, they do not know about controls
on the report.

You can calculate totals a couple of ways depending on the
calculation in the text box. If it's just an expression
with fields from the report's record source table/query,
then you should use a sum of the expression. For example,
if the text box expression is kind of like:
=Quantity * Price
then the total would be:
=Sum(Quantity * Price)

If the calculation is done in code or is not based on fields
in the table/query, then you can use a text box named
txtRunSubTotal in the detail section with an expression
=calculatedtextbox
and set its RunningSum property to Over Group. Then the
group footer subtotal text box's expression would be:
=txtRunSubTotal

The similar arrangement with another detail section text box
named txtRunGrandTotal with the same expression, but with
its RunningSum text box set to Over All. The report footer
text box can then use the expression:
=txtRunGrandTotal
to display the over all total.

You imply that you are calculating the sub total in code,
which is almost always going to cause problems. If so, try
using one of the above approaches instead. If you don't
think you can get what you want, post back with more
information about the subtotal calculation and we'll see if
we can come up with a reliable way to get the required
result.

 

[kai]

Hi,Marsh
Thank you so much for helping. This is the detail info:
I have an table "tblOrder", it lists all the orders by invoice# and second
table "tblInvoiceNumber" has "invoice#" and "InvoiceTotal" fields. I join
these two tables. "InvoiceTotal" is one of fields.

On the report group by "Invoice#", I insert "InvoiceTotal" in the Invoice
footer section as subTotal. It shows right amount, but the problem is when
the invoice has muiltiple items, at report footer, it will be calculated
muiltiple times.

For example, invoice# ="1234" has total amount $100, but it has 7items, then
at end of report, it will show $700 because it the query (tblOrder join
tbltblInvoiceNumber) it shows 7 times.

So I try to sum up the value in the textbox on report not from the field in
the query. When I put a textbox in the Invoice footer section (the same
section with subTotak) and run over group it also calculated muiltiple
times.

I really appreciate your help, I have trying this for long time (two
years).

Kai

I use Access2002. I have a report, on each group I have a subtotal (not
directly from fields, get from on report calculation) with name txtSubTotal.
At report level, I want sum up "txtSubTotal", I use Sum([txtSubTotal]) but
Access pops up a input window asking for parameter for "txtSubTotal". Is it
possible to sum up textbox on a report?

The aggregate functions work with fields in the report's
record source table/query, they do not know about controls
on the report.

You can calculate totals a couple of ways depending on the
calculation in the text box. If it's just an expression
with fields from the report's record source table/query,
then you should use a sum of the expression. For example,
if the text box expression is kind of like:
=Quantity * Price
then the total would be:
=Sum(Quantity * Price)

If the calculation is done in code or is not based on fields
in the table/query, then you can use a text box named
txtRunSubTotal in the detail section with an expression
=calculatedtextbox
and set its RunningSum property to Over Group. Then the
group footer subtotal text box's expression would be:
=txtRunSubTotal

The similar arrangement with another detail section text box
named txtRunGrandTotal with the same expression, but with
its RunningSum text box set to Over All. The report footer
text box can then use the expression:
=txtRunGrandTotal
to display the over all total.

You imply that you are calculating the sub total in code,
which is almost always going to cause problems. If so, try
using one of the above approaches instead. If you don't
think you can get what you want, post back with more
information about the subtotal calculation and we'll see if
we can come up with a reliable way to get the required
result.

 

[Marshall Barton]

Thank you so much for helping. This is the detail info:
I have an table "tblOrder", it lists all the orders by invoice# and second
table "tblInvoiceNumber" has "invoice#" and "InvoiceTotal" fields. I join
these two tables. "InvoiceTotal" is one of fields.

On the report group by "Invoice#", I insert "InvoiceTotal" in the Invoice
footer section as subTotal. It shows right amount, but the problem is when
the invoice has muiltiple items, at report footer, it will be calculated
muiltiple times.

For example, invoice# ="1234" has total amount $100, but it has 7items, then
at end of report, it will show $700 because it the query (tblOrder join
tbltblInvoiceNumber) it shows 7 times.

So I try to sum up the value in the textbox on report not from the field in
the query. When I put a textbox in the Invoice footer section (the same
section with subTotak) and run over group it also calculated muiltiple
times.

OK, now I see what's going on. We'll still use the running
sum approach I outlined before, but a little differently
(since you don't need any of that stuff in the detail
section).

Just add a text box named txtRunGrandTotal to the
**group footer** section (instead of the detail section),
set its expression to:
=subTotal
(the name of the text box bound to the InvoiceTotal field)
or bind it directly to the InvoiceTotal field. Set Its
RunningSum property to Over All.

The report footer text box would still use the same
expression as before:
=txtRunGrandTotal

I think that's all you need to do.
--
Marsh
MVP [MS Access]

I use Access2002. I have a report, on each group I have a subtotal (not
directly from fields, get from on report calculation) with name txtSubTotal.
At report level, I want sum up "txtSubTotal", I use Sum([txtSubTotal]) but
Access pops up a input window asking for parameter for "txtSubTotal". Is it
possible to sum up textbox on a report?

The aggregate functions work with fields in the report's
record source table/query, they do not know about controls
on the report.

You can calculate totals a couple of ways depending on the
calculation in the text box. If it's just an expression
with fields from the report's record source table/query,
then you should use a sum of the expression. For example,
if the text box expression is kind of like:
=Quantity * Price
then the total would be:
=Sum(Quantity * Price)

If the calculation is done in code or is not based on fields
in the table/query, then you can use a text box named
txtRunSubTotal in the detail section with an expression
=calculatedtextbox
and set its RunningSum property to Over Group. Then the
group footer subtotal text box's expression would be:
=txtRunSubTotal

The similar arrangement with another detail section text box
named txtRunGrandTotal with the same expression, but with
its RunningSum text box set to Over All. The report footer
text box can then use the expression:
=txtRunGrandTotal
to display the over all total.

You imply that you are calculating the sub total in code,
which is almost always going to cause problems. If so, try
using one of the above approaches instead. If you don't
think you can get what you want, post back with more
information about the subtotal calculation and we'll see if
we can come up with a reliable way to get the required
result.

 

[kai]

Marshall ,
Follow your instructions, it works perfect. Thank you very much, I am so
excited.

Kai

Thank you so much for helping. This is the detail info:
I have an table "tblOrder", it lists all the orders by invoice# and second
table "tblInvoiceNumber" has "invoice#" and "InvoiceTotal" fields. I join
these two tables. "InvoiceTotal" is one of fields.

On the report group by "Invoice#", I insert "InvoiceTotal" in the Invoice
footer section as subTotal. It shows right amount, but the problem is when
the invoice has muiltiple items, at report footer, it will be calculated
muiltiple times.

For example, invoice# ="1234" has total amount $100, but it has 7items, then
at end of report, it will show $700 because it the query (tblOrder join
tbltblInvoiceNumber) it shows 7 times.

So I try to sum up the value in the textbox on report not from the field in
the query. When I put a textbox in the Invoice footer section (the same
section with subTotak) and run over group it also calculated muiltiple
times.

OK, now I see what's going on. We'll still use the running
sum approach I outlined before, but a little differently
(since you don't need any of that stuff in the detail
section).

Just add a text box named txtRunGrandTotal to the
**group footer** section (instead of the detail section),
set its expression to:
=subTotal
(the name of the text box bound to the InvoiceTotal field)
or bind it directly to the InvoiceTotal field. Set Its
RunningSum property to Over All.

The report footer text box would still use the same
expression as before:
=txtRunGrandTotal

I think that's all you need to do.
--
Marsh
MVP [MS Access]

kai wrote:
I use Access2002. I have a report, on each group I have a subtotal (not
directly from fields, get from on report calculation) with name txtSubTotal.
At report level, I want sum up "txtSubTotal", I use Sum([txtSubTotal]) but
Access pops up a input window asking for parameter for "txtSubTotal".

Is
it

possible to sum up textbox on a report?

The aggregate functions work with fields in the report's
record source table/query, they do not know about controls
on the report.

You can calculate totals a couple of ways depending on the
calculation in the text box. If it's just an expression
with fields from the report's record source table/query,
then you should use a sum of the expression. For example,
if the text box expression is kind of like:
=Quantity * Price
then the total would be:
=Sum(Quantity * Price)

If the calculation is done in code or is not based on fields
in the table/query, then you can use a text box named
txtRunSubTotal in the detail section with an expression
=calculatedtextbox
and set its RunningSum property to Over Group. Then the
group footer subtotal text box's expression would be:
=txtRunSubTotal

The similar arrangement with another detail section text box
named txtRunGrandTotal with the same expression, but with
its RunningSum text box set to Over All. The report footer
text box can then use the expression:
=txtRunGrandTotal
to display the over all total.

You imply that you are calculating the sub total in code,
which is almost always going to cause problems. If so, try
using one of the above approaches instead. If you don't
think you can get what you want, post back with more
information about the subtotal calculation and we'll see if
we can come up with a reliable way to get the required
result.