Sum of NZ column

[corey1959]

New here...and fairly new to Access...but having an issue trying to sum a column. I've done an extensive Google search on this and I can't figure it out.

I have a report that has a column called Net Profit. The formula I'm using is this:

=Nz([NetSales],0)-Nz([GrandTotal],0)

That works great to get the horizontal total of taking the NetSales and subtracting the Grand Total.

Let's say...for simplicity sakes....that I have the following numbers:


NET SALES GRAND TOTAL NET PROFIT
$1000 $500 $500
$400 $200 $200
$700 $400 $300
$300 $100 $200

HOW can I total the Net Profit line so that it shows $1200 for the vertical column?

Or is it even possible???

Thanks

Mike

Edit: The columns won't line up after submitting....pretty much my day today....

[corey1959]

And I've tried this:

=Sum(Nz([NetSales],0)-Nz([GrandTotal],0))

[June7]

That expression should work. Is it in report footer section?

To get aligned 'columns' in the post, use the table builder in Advanced post editor.

[orange]

What did that formula give as result? Not sure why it isn't working.

You could try (guess only)

=Sum(Nz([NetSales],0)) - Sum(Nz([GrandTotal],0))


OOoops: I see June has responded while I was typing.

[corey1959]

Yes...it is in the report footer and I've tried it in the page footer.

When I run either this:

=Sum(Nz([NetSales],0)-Nz([GrandTotal],0))

or this:

=Sum(Nz([NetSales],0)) - Sum(Nz([GrandTotal],0))

When I switch over to view the report, two dialog boxes pop up looking for a value for NetSales and GrandTotal. I have these named that in the Name properties of both sources. The names should....in theory....be OK since the code is working horizontally...

Thanks for the replies. I am sure something simple is wrong.

Mike

edit: Once I click past the dialog boxes that pop up looking for a value for Net Sales and GrandTotal, the value returns as $0.00.

[June7]

Are those the actual field names? Aggregate functions must reference fields, not controls.

[corey1959]

When I click on either field and bring up the properties box, under the "Name" box are those names. Not sure if this makes any difference, but the field called GrandTotal is generated using this:

=Nz([SaleLotCost],0)+Nz([ExpenseCost],0)

Again, those fields are named SaleLotCost and ExpenseCost in the properties Name box.

Mike

[corey1959]

BTW...I agree with you all that the =Sum(Nz([NetSales],0)-Nz([GrandTotal],0)) "should" work....

[June7]

Then GrandTotal is not a field, it is just an unbound textbox with an expression. Options:

1. do the simple arithmetic in the form's RecordSource query so there will be fields that can be referenced by the Sum() function

2. do calc in textbox that uses the raw data fields

=Sum(Nz([NetSales],0) - Nz([SaleLotCost],0) + Nz([ExpenseCost],0))

Now is NetSales actually a field or just another unbound textbox with an expression?

[corey1959]

Ahhhh....

NetSales is an unbound text box with an expression. Specifically:

=Sum([PayPalPayment]-[Shipping]-[EbayListingFee]-[EbaySellingFee]-[PayPalFee])

So...with that....does that change anything else?

Mike

[June7]

Previous comments still apply. Adjust query and/or textbox expressions as needed.

Might find this of interest http://allenbrowne.com/QueryPerfIssue.html

[corey1959]

But do I need to change that NetSales field to reference my line of code that I posed above?

When I run your code, it is returning a value, but it is still bringing up a dialog box looking for a value for NetSales. I'm guessing I need to reference this: =Sum([PayPalPayment]-[Shipping]-[EbayListingFee]-[EbaySellingFee]-[PayPalFee]) in your code.

Mike

[corey1959]

I think I got it I'm going to have to check the numbers tomorrow but it looks right.

I entered this:

=Sum(Nz([PayPalPayment]-[Shipping]-[EbayListingFee]-[EbaySellingFee]-[PayPalFee],0)-Nz([SaleLotCost],0)+Nz([ExpenseCost],0))

Mike

[June7]

Yep, as I said, adjust where needed.

Aggregate functions must refer to fields, not controls.

Remember, arithmetic with Null returns null. If any field of the NetSales calc is Null the return will be Null. Don't you want a number if any of the fields have a value? Or if one has data all 5 will?

[ssanfu]

PMFJI,

Is the record source for the report a table or query?

I would do the calculation(s) in a query as June suggested (post #9 - point #1).
Then it is a matter of summing controls in the footer for the 3 (virtual - aka calculated) fields.