I have a query that sums up prices for items that meet certain criteria. It works fine when there are records that meet the criteria. However, when there are no records that meet the criteria I want it to return with a sum of zero. This zero gets used in other queries/reports. I've tried multiple ways of fixing this (Nz, IIf, etc.), yet nothing seems to work.
Here is the SQL code from the query:
SELECT [Account Numbers].[Account Number], Sum(Orders.[Total Item Price]) AS [SumOfTotal Item Price]
FROM (Orders INNER JOIN [Collection Development Request] ON Orders.[Bib ID] = [Collection Development Request].ID) INNER JOIN [Account Numbers] ON [Collection Development Request].Type = [Account Numbers].[Item Type]
GROUP BY [Account Numbers].[Account Number], Orders.Ordered, Orders.Received
HAVING ((([Account Numbers].[Account Number])=61000) AND ((Orders.Ordered)=Yes) AND ((Orders.Received)=No));
And here is a screenshot of the display view:
Any help is appreciated. Please let me know if you need more info.
Thanks