CODEWITHSUNDEEP
ms-accessms-access-2007
Tom
Tom

Reputation: 301

Calculated Query Field ignoring some results

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]

Upvotes: 1

Views: 27

Answers (1)

HansUp
HansUp

Reputation: 97111

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.

Upvotes: 1

Related Questions