Calculated Query Field ignoring some results

Question

I have a calculated field in a query that takes our total revenue per customer and subtracts out total costs per customer to get our margins with that customer. The formula works fine as long as the customer has a cost value but if the customer has no cost, the formula is ignoring that revenues exists and is excluding that customer from the total and the query does not include them in the returned result. Is there a way to fix the formula to include these customers with no costs but still generate revenue?

SELECT [Company Information].[Company Name], ([2 YTD - Customer 
Revenues].SumOfSumOfCharge-[2 YTD - Customer Costs].[SumOfTotal Cost]) 
AS [Customer Margin]

Answer 1

For a query run from within an Access session, you can use Nz to have Access substitute 0 for Null when it computes the difference ...

Nz([2 YTD - Customer Costs].[SumOfTotal Cost], 0) 

Alternatively you could use an IIf expression to accomplish the same thing ...

IIf([2 YTD - Customer Costs].[SumOfTotal Cost] Is Null, 0, [2 YTD - Customer Costs].[SumOfTotal Cost])

Support for IIf is built into the db engine, so it is available for any Access query. Nz is an external (VBA) function which the db engine can use within an Access session. So the IIf approach should theoretically be faster, but may not be noticeably faster in practice.