Retuen Zero Value in a query

[dg875]

Hi all, I am trying to work out a simple profit/loss number in a query. I have an "Income" table and a "Cost" table. Profit/loss is obviously is Income - Cost. Sometimes however, there is no income. When that is the case I want my query to display a zero, so the end result would be a loss equal to the amount of costs. At moment access leaves it blank. Many Thanks for your help & time.

 

[Banana]

Look around for posts on Nz function. (Null to Zero).

 

[dg875]

Yeah - I have seen those, but when I use that function access omits the income record completely

 

[Banana]

Ah, then you must have enclosed the whole thing in Nz.

You want two Nzs

Expr1: Nz(Field1) - Nz(Field2)

(Check for proper syntax as this is off the top of my head)

 

[xxyy]

Hi!

http://www.access-programmers.co.uk/forums/showthread.php?t=103636

The last remark (on the bottom of the page) may be helpful.

BR

SzCs