Negative Sum in Text Box

[klynch0803]

I have a report that is calculating from four fields it could be a positive number or a negative..

What is wrong with this?

=Sum(nz([Sales],"0"))-nz([Payroll]+[InvPurch]+[nonInvPurch]),"0"))

 

[missinglinq]

=Sum(nz([Sales],"0"))-nz([Payroll]+[InvPurch]+[nonInvPurch]),"0"))

I'm not exactly sure what you're trying to do here, so I'm not sure about where your parentheses should be, but I do know that in anything like this you have to have an equal number of left-handed and right-handed parentheses, and you have 3 left-handed parentheses and 5 right-handed parentheses!

 

[klynch0803]

=Sum(nz([Sales],"0"))-nz([Payroll]+[InvPurch]+[nonInvPurch]),"0"))

I'm not exactly sure what you're trying to do here, so I'm not sure about where your parentheses should be, but I do know that in anything like this you have to have an equal number of left-handed and right-handed parentheses, and you have 3 left-handed parentheses and 5 right-handed parentheses!

I'm trying to take my "Sales" and subtract "Payroll", "InvPurch" and "nonInvPurch" from it leaving me my net Profit.

 

[missinglinq]

This should do it:

=Sum (Nz([Sales], 0) - (Nz([Payroll], 0) + Nz([InvPurch], 0) + Nz([nonInvPurch], 0)))


The problem with your code

=Sum(nz([Sales],"0"))-nz([Payroll]+[InvPurch]+[nonInvPurch]),"0"))

besides not having the parentheses balanced, is that you have to use the Nz() function with each component of the calculation. NULL + anything is NULL, so

nz([Payroll]+[InvPurch]+[nonInvPurch]),"0"))

means that if [Payroll] or [InvPurch] or [nonInvPurch] was NULL then

[Payroll] + [InvPurch] + [nonInvPurch] would be NULL and would be replaced using the Nz() function, which brings us to the last problem.

nz([Sales],"0")

doesn't replace a NULL value with zero (a number value) but rather with a string "0" and you don't do math with a string!

Let me know how you get on!

 

[CEH]

Not to be picky here.... but....
If anyone who is use to looking at income statements is going to be looking at this I would change your setup a bit.... Usually you will see "Sales" less "cost of goods sold" and show net sales. Then the remainder of your expenses would be deducted from your net sales. Your "Cost of goods sold" is beginning inventory plus purchases less ending inventory. Just getting it a little closer to GAAP.... You might also want to remember that "Sales" account balance (if you have sales) will be a credit figure.