Totals in reports

[jpark3205]

I have a report that is based of a query. I am trying to total two columns
across. I have it working except that if there is no information it leaves
the column blank. For example,

Loans Deductions Total
$100 $50 $50
$40
$200
what I would like for it to do is say $-40 on the second row and $200 on the
third row. The expression I am using right now is =([Loans])-([Deductions]).
These are both summed items in the query. Is there a way to do this?

Thanks for your help!

 

[Jerry Whittle]

=(NZ([Loans],0)-NZ([Deductions],0)

 

[jpark3205]

Thank you so much! it worked like a charm!

=(NZ([Loans],0)-NZ([Deductions],0)
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a report that is based of a query. I am trying to total two columns
across. I have it working except that if there is no information it leaves
the column blank. For example,

Loans Deductions Total
$100 $50 $50
$40
$200
what I would like for it to do is say $-40 on the second row and $200 on the
third row. The expression I am using right now is =([Loans])-([Deductions]).
These are both summed items in the query. Is there a way to do this?

Thanks for your help!

 

[BruceM]

Loans is probably null. Null is essentially "unknown". Subtracting from
null leaves you with null, so you need to convert null to 0:
=Nz([Loans],0) - Nz([Deductions],0)
Help has more information about Nz.

You could use 0 as the Default Value for Loans and Deductions, but that
won't affect existing records. If you like you can use an update query to
change Null to 0 in those fields, but the Nz function should do what you
need.