Summing when no records meet criteria

[clew3]

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

[June7]

What items do you want to return a record for even if related data is not existing?

Try changing one or both of the jointypes from INNER to LEFT or RIGHT.

[clew3]

I don't want to return any items. I just need to know price totals for items with matching criteria. If there are no items with matching criteria, I need it to sum a price total of $0.00.

Changing the jointype doesn't help, unless that is the reason why one of the expressions I've tried (Nz, IIf) doesn't work.

[June7]

True, changing join type probably won't really help if the Yes and No criteria are used.

Can't sum or count something that isn't there. So remove criteria then can use IIf or Nz expressions.

Nz([Total Item Price],0)

[clew3]

But if I remove criteria how do I get it to sum only certain records when there are records that match the criteria?

[June7]

Try creating a field with IIf expression then Sum that field.

IIf(Ordered=Yes AND Received = No, [Total Item Price], 0)

Instead of grouping in a query you might consider doing the grouping and sorting with aggregate calcs in a report. This will allow display of detail records and summary data.

[clew3]

That worked! I thought it was something along those lines, but I just wasn't sure how to build the IIf expression. Thanks!