Prev: Print to PDF with Diacritics snafu
Next: Expression Builder; embedded data from other reports in Summary re
From: Kay on 17 Mar 2010 17:28
HI all,

I am trying to sum the total of three fields in the report header. Seems
easy enough and I understand how to do that in a typcial situation. But,
here is my situation. The report is for government compliance, so the report
includes to entirely and unrelated tables. Therefore, I have two subreports.
In the report header, I must show the grand total from each sub report which
I have done. A third field's value is derived from an input box as the
information is arbitrarily supplied from a different department. Now, I need
to sum all three fields. Since you cannot use the Name of the control, I
assumed I would need to completely reference the fields in the final
calculation, but, I only get error messages. Please let me know if this is
even possible. Thanks so much!

The calculations in the report that provide the values for each calculated
control are below:
=FormatCurrency(sbrptDCGiftExpenses.Report!GrandTotalGift)
=FormatCurrency(sbrptDCAdv.Report!GrandTotalAdv)
=FormatCurrency(InputBox("Enter the Aggregate Cost of employees engaged in
promotional activites in the District of Columbia"))
From: Marshall Barton on 17 Mar 2010 19:06
Kay wrote:
>I am trying to sum the total of three fields in the report header. Seems
>easy enough and I understand how to do that in a typcial situation. But,
>here is my situation. The report is for government compliance, so the report
>includes to entirely and unrelated tables. Therefore, I have two subreports.
> In the report header, I must show the grand total from each sub report which
>I have done. A third field's value is derived from an input box as the
>information is arbitrarily supplied from a different department. Now, I need
>to sum all three fields. Since you cannot use the Name of the control, I
>assumed I would need to completely reference the fields in the final
>calculation, but, I only get error messages. Please let me know if this is
>even possible. Thanks so much!
>
>The calculations in the report that provide the values for each calculated
>control are below:
>=FormatCurrency(sbrptDCGiftExpenses.Report!GrandTotalGift)
>=FormatCurrency(sbrptDCAdv.Report!GrandTotalAdv)
>=FormatCurrency(InputBox("Enter the Aggregate Cost of employees engaged in
>promotional activites in the District of Columbia"))


You can not sum text strings so you need to get rid of the
FormatCurrency calls (all the Format... functions return
text). Instead, set each text box's Format property to
Currency.

Then you can have a text box that displays the total of the
three text boxes by using an expression like:

=firsttextbox + secondtextbox + thirdtextbox

--
Marsh
MVP [MS Access]
From: Kay on 18 Mar 2010 13:11
Marshall,

Thanks that worked perfectly. I was surprised that the formatcurrency is
translated as text. I feared it was more complex than that...wouldn't you
know.

"Kay" wrote:

> HI all,
>
> I am trying to sum the total of three fields in the report header. Seems
> easy enough and I understand how to do that in a typcial situation. But,
> here is my situation. The report is for government compliance, so the report
> includes to entirely and unrelated tables. Therefore, I have two subreports.
> In the report header, I must show the grand total from each sub report which
> I have done. A third field's value is derived from an input box as the
> information is arbitrarily supplied from a different department. Now, I need
> to sum all three fields. Since you cannot use the Name of the control, I
> assumed I would need to completely reference the fields in the final
> calculation, but, I only get error messages. Please let me know if this is
> even possible. Thanks so much!
>
> The calculations in the report that provide the values for each calculated
> control are below:
> =FormatCurrency(sbrptDCGiftExpenses.Report!GrandTotalGift)
> =FormatCurrency(sbrptDCAdv.Report!GrandTotalAdv)
> =FormatCurrency(InputBox("Enter the Aggregate Cost of employees engaged in
> promotional activites in the District of Columbia"))
From: Marshall Barton on 18 Mar 2010 18:46
Kay wrote:
>I was surprised that the formatcurrency is
>translated as text.

Think about it. When something is formatted it can look
like almost anything. Even the built-in Currency format
includes $ and for negative values ( ). So the result of a
Format... function MUST be a text string.

A general guideline is to use a text box's Format property
to format a value for display. It's only when a text box
expression is concatenating a value with some other text
that it makes some kind of sense to use a Format...
function. Eg:
="Bottom Line: " & Format(Amount, "Currency")

Even then, you could use the Format property and get a
fancier display if you have a mind to:
[Green]"Profit: "$0.00;[Red]"Loss: "$(0.00);"All Even"

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Print to PDF with Diacritics snafu
Next: Expression Builder; embedded data from other reports in Summary re