Sum Question

[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?

 

[Guest]

Maybe try
=Sum([DepositAmount])-Sum([WithdrawalAmount])

 

[Guest]

It worked.
Thank you

Maybe try
=Sum([DepositAmount])-Sum([WithdrawalAmount])

--
Duane Hookom
Microsoft Access MVP

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?

 

[Pat Hartman \(MVP\)]

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.

 

[Guest]

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

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.

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?

 

[Pat Hartman \(MVP\)]

You're welcome, you're welcome

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

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.

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?