Report sum problem

[Guest]

I have a report that uses a query to display deposit and withdrawal amounts
from a table. I'm trying to get the report to calculate the balance at the
bottom, but using =Sum([Deposits]-[Withdrawals]) it just returns incorrect
amounts. What am I doing wrong?
Thank you.

 

[Debra Farnham]

When you say "bottom", do you mean you have placed it in the report footer?

Debra

 

[Guest]

Yes, I made that mistake earlier, but it is definitley in the report footer.
The individual totals for withdrawal and deposits work correctly
=Sum([Withdrawals])
and
=Sum([Deposts])
but it fails to subtract withdrawals from deposits and show the balance
using the syntax above.

 

[Debra Farnham]

I'm thinking you want

=Sum([Withdrawals]) - Sum([Deposits])

 

[Debra Farnham]

Oops ... think I got that a little backwards

=sum([Deposits]) - Sum([Withdrawals])

I'm thinking you want

=Sum([Withdrawals]) - Sum([Deposits])

Yes, I made that mistake earlier, but it is definitley in the report
footer.
The individual totals for withdrawal and deposits work correctly
=Sum([Withdrawals])
and
=Sum([Deposts])
but it fails to subtract withdrawals from deposits and show the balance
using the syntax above.

 

[Guest]

Thank you!

 

[Steve Schapel]

Or...
=Sum(Nz([Deposits],0)-Nz([Withdrawals],0))

 

[Xfree]

Steve,
What are the Nx and ,0 doing for the below statment?

Or...
=Sum(Nz([Deposits],0)-Nz([Withdrawals],0))

--
Steve Schapel, Microsoft Access MVP

Oops ... think I got that a little backwards

=sum([Deposits]) - Sum([Withdrawals])

 

[strive4peace]

NZ (not nX) is a function to convert a Null value to something else --
the second argument, optional, is what you want the value to be if the
first argument is null

If you are adding
Null + anything = Null

one way that Null can be treated like a zero is to "wrap" it in the NZ
function

when you are doing arithmetic operations with numeric fields that may
not be filled out, it is best to wrap the reference with NZ

Nz([fieldname],0) -->
1. if [fieldname] has a value, it will be used
2. if not, 0 (zero) will be used


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Steve,
What are the Nx and ,0 doing for the below statment?

Or...
=Sum(Nz([Deposits],0)-Nz([Withdrawals],0))

--
Steve Schapel, Microsoft Access MVP

Oops ... think I got that a little backwards

=sum([Deposits]) - Sum([Withdrawals])