Summing Calculated Fields

[Guest]

Hi! In my database form, I have several fields that are calculated. I want
the query to sum those fields but cannot do it. I get different error
messages so I am defining the fields in hopes that someone can help.

In the form:
Control Source for [TotalDue] is
=([RegFee]+[RegPreConfFee]+[MealsTotal]+[GiftShopTotal]+[CEUFee]+[ScholContrib]+[AdvocContrib])

[BalDue1] is the remaining balance after the 1st payment – necessary because
some have 2 payments
Control Source for [BalDue1] is:
=([TotalDue]-[TotalPaid1])

[BalDue2] is the remaining balance after the 2nd payment – hopefully $0 but
occasionally not true so needs to be included in the report.
Control Source for [BalDue2] is:
=([TotalDue]-[TotalPaid1]-[TotalPaid2])

[GrdTotPaid] is the total of both payments.
Control Source for [GrdTotPaid] is:
=([TotalDue]-[TotalPaid1]-[TotalPaid2])

These work fine in the form or in a report showing individual records, but
when I try to sum the amounts for a cumulative report, I cannot get a sum for
the fields that used calculations.

Thanks in advance!

 

[John Spencer (MVP)]

What you may need to do is repeat the TOTALDUE calculation in your other Calculations.

BALDUE1: [RegFee]+[RegPreConfFee]+[MealsTotal]+[GiftShopTotal]+[CEUFee]+[ScholContrib]+[AdvocContrib]-TotalPaid1
Baldue2: [RegFee]+[RegPreConfFee]+[MealsTotal]+[GiftShopTotal]+[CEUFee]+[ScholContrib]+[AdvocContrib]-TotalPaid1-TotalPaid2

 

[Marshall Barton]

Hi! In my database form, I have several fields that are calculated. I want
the query to sum those fields but cannot do it. I get different error
messages so I am defining the fields in hopes that someone can help.

In the form:
Control Source for [TotalDue] is:
=([RegFee]+[RegPreConfFee]+[MealsTotal]+[GiftShopTotal]+[CEUFee]+[ScholContrib]+[AdvocContrib])

[BalDue1] is the remaining balance after the 1st payment – necessary because
some have 2 payments
Control Source for [BalDue1] is:
=([TotalDue]-[TotalPaid1])

[BalDue2] is the remaining balance after the 2nd payment – hopefully $0 but
occasionally not true so needs to be included in the report.
Control Source for [BalDue2] is:
=([TotalDue]-[TotalPaid1]-[TotalPaid2])

[GrdTotPaid] is the total of both payments.
Control Source for [GrdTotPaid] is:
=([TotalDue]-[TotalPaid1]-[TotalPaid2])

These work fine in the form or in a report showing individual records, but
when I try to sum the amounts for a cumulative report, I cannot get a sum for
the fields that used calculations.

What John says applies to using the Sum function, but there
is another way to calculate a grand total in a report.

For example, add an invisible text box named txtRunTotPaid
in the same section as the GrdTotPaid. Set it control
source expression to =GrdTotPaid and its RunningSum property
to Over All. Then the grand total text box in the report
footer can display the grand total by using the expression
=txtRunTotPaid

 

[Guest]

Thanks to both of you. I will try both but feel confident that you have
solved my problem!
--
nhb -- nc

Hi! In my database form, I have several fields that are calculated. I want
the query to sum those fields but cannot do it. I get different error
messages so I am defining the fields in hopes that someone can help.

In the form:
Control Source for [TotalDue] is:
=([RegFee]+[RegPreConfFee]+[MealsTotal]+[GiftShopTotal]+[CEUFee]+[ScholContrib]+[AdvocContrib])

[BalDue1] is the remaining balance after the 1st payment – necessary because
some have 2 payments
Control Source for [BalDue1] is:
=([TotalDue]-[TotalPaid1])

[BalDue2] is the remaining balance after the 2nd payment – hopefully $0 but
occasionally not true so needs to be included in the report.
Control Source for [BalDue2] is:
=([TotalDue]-[TotalPaid1]-[TotalPaid2])

[GrdTotPaid] is the total of both payments.
Control Source for [GrdTotPaid] is:
=([TotalDue]-[TotalPaid1]-[TotalPaid2])

These work fine in the form or in a report showing individual records, but
when I try to sum the amounts for a cumulative report, I cannot get a sum for
the fields that used calculations.

What John says applies to using the Sum function, but there
is another way to calculate a grand total in a report.

For example, add an invisible text box named txtRunTotPaid
in the same section as the GrdTotPaid. Set it control
source expression to =GrdTotPaid and its RunningSum property
to Over All. Then the grand total text box in the report
footer can display the grand total by using the expression
=txtRunTotPaid

 

[Guest]

You were right and I appreciate the help. Now I have another problem. I
need to create a report showing people who still have a balance due. The
query does not work if I use the criterial >0 in the calculated field. Can
you tell me how to identify only those people where [BalDue2] is greater than
zero? Hope so and thanks!
--
nhb -- nc

Thanks to both of you. I will try both but feel confident that you have
solved my problem!
--
nhb -- nc

Hi! In my database form, I have several fields that are calculated. I want
the query to sum those fields but cannot do it. I get different error
messages so I am defining the fields in hopes that someone can help.

In the form:
Control Source for [TotalDue] is:
=([RegFee]+[RegPreConfFee]+[MealsTotal]+[GiftShopTotal]+[CEUFee]+[ScholContrib]+[AdvocContrib])

[BalDue1] is the remaining balance after the 1st payment – necessary because
some have 2 payments
Control Source for [BalDue1] is:
=([TotalDue]-[TotalPaid1])

[BalDue2] is the remaining balance after the 2nd payment – hopefully $0 but
occasionally not true so needs to be included in the report.
Control Source for [BalDue2] is:
=([TotalDue]-[TotalPaid1]-[TotalPaid2])

[GrdTotPaid] is the total of both payments.
Control Source for [GrdTotPaid] is:
=([TotalDue]-[TotalPaid1]-[TotalPaid2])

These work fine in the form or in a report showing individual records, but
when I try to sum the amounts for a cumulative report, I cannot get a sum for
the fields that used calculations.

What John says applies to using the Sum function, but there
is another way to calculate a grand total in a report.

For example, add an invisible text box named txtRunTotPaid
in the same section as the GrdTotPaid. Set it control
source expression to =GrdTotPaid and its RunningSum property
to Over All. Then the grand total text box in the report
footer can display the grand total by using the expression
=txtRunTotPaid

 

[Marshall Barton]

You were right and I appreciate the help. Now I have another problem. I
need to create a report showing people who still have a balance due. The
query does not work if I use the criterial >0 in the calculated field. Can
you tell me how to identify only those people where [BalDue2] is greater than
zero?

THis is a totally separate problem and should be posted to a
new thread so more folks will look at it.

Please post the query, there should be a way to make that
work.