Report Formula

[Sr Accountant]

I have a report that I am trying to get my net charges returned. I have 5
columns that I would like to net so that I end up with a column 6 value.
Some of the columns may not have any data in them. How can I get my SUM
function to actually return a value?

Example:
Column 1 = Charges (Total Charges)
Column 2 = A Charges (Location A Charges)
Column 3 = B Charges (Location B Charges)
Column 4 = C Charges (Location C Charges)
Column 5 = D Charges (Location D Charges)
Column 6 = Charges-A Charges-B Charges-C Charges-D Charges

How can I make this work for me? Everything else is working beautifully on
my report.

Thank you in advance for your help!

 

[Clifford Bass]

Hi,

Null by definition represents an unknown value. Adding an unknown
value to a known value gives back an unknown value--that is null. Try using
Nz(column, 0) function to turn any nulls into zeros. See the Nz() function
help for further details.

[Column 6] = Nz([Charges-A], 0) + Nz([Charges-B], 0) + Nz([Charges-C], 0) +
Nz([Charges-D], 0) + Nz([Charges], 0)

Hope this helps,

Clifford Bass

 

[Sr Accountant]

That worked perfect. Just exactly what I needed. Thank you very much
Clifford.

 

[Marshall Barton]

I have a report that I am trying to get my net charges returned. I have 5
columns that I would like to net so that I end up with a column 6 value.
Some of the columns may not have any data in them. How can I get my SUM
function to actually return a value?

Example:
Column 1 = Charges (Total Charges)
Column 2 = A Charges (Location A Charges)
Column 3 = B Charges (Location B Charges)
Column 4 = C Charges (Location C Charges)
Column 5 = D Charges (Location D Charges)
Column 6 = Charges-A Charges-B Charges-C Charges-D Charges

Column 6 should use an expression like:

=Nz(Charges,0) - Nz([A Charges],0) - Nz([B Charges],0) -
Nz([C Charges],0) - Nz([D Charges],0)

A text box in a group and/or report footer section can total
column 6 by using an expression like:

=Sum(Nz(Charges,0) - Nz([A Charges],0) - Nz([B Charges],0) -
Nz([C Charges],0) - Nz([D Charges],0))

Note that **all** of the charges names must be the name of a
field in the report's record source table/query. They can
not be the names of controls in the reprot.

 

[Clifford Bass]

Hi Marshall,

I see from your answer that I misunderstood what Sr. Accountant was
wanting. Thanks for providing the proper answer. One suggestion I would add
to your answer is that he move the calculation of the sixth column into the
report's source query as a calculated column. Then he can just directly sum
that calculated column instead of making the report redo the calculation a
second time in order to do the sum.

Clifford Bass

I have a report that I am trying to get my net charges returned. I have 5
columns that I would like to net so that I end up with a column 6 value.
Some of the columns may not have any data in them. How can I get my SUM
function to actually return a value?

Example:
Column 1 = Charges (Total Charges)
Column 2 = A Charges (Location A Charges)
Column 3 = B Charges (Location B Charges)
Column 4 = C Charges (Location C Charges)
Column 5 = D Charges (Location D Charges)
Column 6 = Charges-A Charges-B Charges-C Charges-D Charges

Column 6 should use an expression like:

=Nz(Charges,0) - Nz([A Charges],0) - Nz([B Charges],0) -
Nz([C Charges],0) - Nz([D Charges],0)

A text box in a group and/or report footer section can total
column 6 by using an expression like:

=Sum(Nz(Charges,0) - Nz([A Charges],0) - Nz([B Charges],0) -
Nz([C Charges],0) - Nz([D Charges],0))

Note that **all** of the charges names must be the name of a
field in the report's record source table/query. They can
not be the names of controls in the reprot.