Report Formula

  • Thread starter Thread starter Sr Accountant
  • Start date Start date
S

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!
 
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 said:
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
Click to expand...

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.
 
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

Marshall Barton said:
Sr said:
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
Click to expand...

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.
Click to expand...
 
Back
Top