Sum Question

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

Guest

I have the following calculated field under the report footer section of
report:
=Sum([DepositAmount]-[WithdrawalAmount])
However it does not provide the total balance of what is left over once
withdrawals have been subtracted from the deposits.
=Sum([WithdrawalAmount])
=Sum([DepositAmount])
The two fields above is what is being used for the final calculation and
they provide the totals with no problems so what could it be?
 
It worked.
Thank you

Duane Hookom said:
Maybe try
=Sum([DepositAmount])-Sum([WithdrawalAmount])

--
Duane Hookom
Microsoft Access MVP


jk said:
I have the following calculated field under the report footer section of
report:
=Sum([DepositAmount]-[WithdrawalAmount])
However it does not provide the total balance of what is left over once
withdrawals have been subtracted from the deposits.
=Sum([WithdrawalAmount])
=Sum([DepositAmount])
The two fields above is what is being used for the final calculation and
they provide the totals with no problems so what could it be?
Click to expand...
Click to expand...
 
The problem is probably caused because some rows have null values in
DepositAmount or WithdrawalAmount. Duane's solution works because the
aggregate functions ignore nulls so sum(aa) - sum(bb) will work fine. In
other situations you may have to use the Nz() function --> sum(Nz(aa,0) -
Nz(bb,0)) -- notice that each operand in the expression must be cleaned of
nulls. Nz(Sum(aa - bb)) will not return the same results.
 
This answer from Pat Hartment solved 2 days of looking for an answer. Thank
you thank you.
--
cjw


Pat Hartman (MVP) said:
The problem is probably caused because some rows have null values in
DepositAmount or WithdrawalAmount. Duane's solution works because the
aggregate functions ignore nulls so sum(aa) - sum(bb) will work fine. In
other situations you may have to use the Nz() function --> sum(Nz(aa,0) -
Nz(bb,0)) -- notice that each operand in the expression must be cleaned of
nulls. Nz(Sum(aa - bb)) will not return the same results.


jk said:
I have the following calculated field under the report footer section of
report:
=Sum([DepositAmount]-[WithdrawalAmount])
However it does not provide the total balance of what is left over once
withdrawals have been subtracted from the deposits.
=Sum([WithdrawalAmount])
=Sum([DepositAmount])
The two fields above is what is being used for the final calculation and
they provide the totals with no problems so what could it be?
Click to expand...
Click to expand...
 
You're welcome, you're welcome:)

Charles said:
This answer from Pat Hartment solved 2 days of looking for an answer.
Thank
you thank you.
--
cjw


Pat Hartman (MVP) said:
The problem is probably caused because some rows have null values in
DepositAmount or WithdrawalAmount. Duane's solution works because the
aggregate functions ignore nulls so sum(aa) - sum(bb) will work fine. In
other situations you may have to use the Nz() function --> sum(Nz(aa,0) -
Nz(bb,0)) -- notice that each operand in the expression must be cleaned
of
nulls. Nz(Sum(aa - bb)) will not return the same results.


jk said:
I have the following calculated field under the report footer section of
report:
=Sum([DepositAmount]-[WithdrawalAmount])
However it does not provide the total balance of what is left over once
withdrawals have been subtracted from the deposits.
=Sum([WithdrawalAmount])
=Sum([DepositAmount])
The two fields above is what is being used for the final calculation
and
they provide the totals with no problems so what could it be?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top