Sum field in a report

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

Guest

I've tried to solve this problem a number of different ways, but am just not
getting it. Please help.

I have a report grouped by ID number. The detail of the group has fields
txtDonation (=[Donation]),
txtConsideration (=IIf(IsNull([Consideration]),0,[Consideration]),
and
txtNet_donation
(=IIf(IsNull([txtConsideration]),[txtDonation],[txtDonation]-[txtConsideration])

The report is based on a query.

I want to add another field, txtTotal_net_donation, that adds all values for
txtNet-donation for that ID.

I believe the field should go in the group footer.

I wrote an expression, but the txtTotal_net_donations will not calculate at
all if txtConsideration is null.

Very frustrating. I've tried the minimal knowledge I have of expression
builder. Tried adding a new field to the query (got an aggregate function
error) and tried a new query (got a circular reference error).

It shouldn't be this difficult, but I am lost.

Please help.

Thank you.
 
jdb said:
I've tried to solve this problem a number of different ways, but am just not
getting it. Please help.

I have a report grouped by ID number. The detail of the group has fields
txtDonation (=[Donation]),
txtConsideration (=IIf(IsNull([Consideration]),0,[Consideration]),
and
txtNet_donation
(=IIf(IsNull([txtConsideration]),[txtDonation],[txtDonation]-[txtConsideration])

The report is based on a query.

I want to add another field, txtTotal_net_donation, that adds all values for
txtNet-donation for that ID.

I believe the field should go in the group footer.

I wrote an expression, but the txtTotal_net_donations will not calculate at
all if txtConsideration is null.
Click to expand...


The issue is that the aggregate functions (Count, Sum, etc)
only operate on **fields** in the form/report's record
source table/query. They are totally unaware of controls on
the form/report.

What that boils down to in your question is that the group
footer text box should use the expression:

=Sum([txtDonation] - Nz([txtConsideration], 0))

Note the use of Nz to simplify your IIf expression

Other simplifications are the txtDonation text box should
not have an = sign. its control source should just be:
Donation
The txtConsideration text box can also use the Nz function:
=Nz([Consideration], 0)
 
Perfect! Thank you.
--
Joe


Marshall Barton said:
jdb said:
I've tried to solve this problem a number of different ways, but am just not
getting it. Please help.

I have a report grouped by ID number. The detail of the group has fields
txtDonation (=[Donation]),
txtConsideration (=IIf(IsNull([Consideration]),0,[Consideration]),
and
txtNet_donation
(=IIf(IsNull([txtConsideration]),[txtDonation],[txtDonation]-[txtConsideration])

The report is based on a query.

I want to add another field, txtTotal_net_donation, that adds all values for
txtNet-donation for that ID.

I believe the field should go in the group footer.

I wrote an expression, but the txtTotal_net_donations will not calculate at
all if txtConsideration is null.
Click to expand...


The issue is that the aggregate functions (Count, Sum, etc)
only operate on **fields** in the form/report's record
source table/query. They are totally unaware of controls on
the form/report.

What that boils down to in your question is that the group
footer text box should use the expression:

=Sum([txtDonation] - Nz([txtConsideration], 0))

Note the use of Nz to simplify your IIf expression

Other simplifications are the txtDonation text box should
not have an = sign. its control source should just be:
Donation
The txtConsideration text box can also use the Nz function:
=Nz([Consideration], 0)
Click to expand...
 
Back
Top