Query based report where query has no result - populate with a zero

[vljones]

I have a report with sub reports. The sub reports are query based. The queries perform a Count. Where no data exists in the table, no results are provided.

Is there a way to populate the sub report field with a 0 when this occurs?

Have tried IIF function in query with no luck.

SELECT QuoteItems.Quot eID, QuoteItems.Clea nID, QuoteItems.Cont actID, IIf(([CLeanID]) Is Null,0,Count([CleanID])) AS CountOfCleanID
FROM QuoteItems
GROUP BY QuoteItems.Quot eID, QuoteItems.Clea nID, QuoteItems.Cont actID
HAVING (((QuoteItems.Q uoteID)=[Forms]![quotation]![QuoteID]) AND ((QuoteItems.Cl eanID)=1));

[hjozinovic]

Try this:

SELECT QuoteItems.QuoteID, QuoteItems.CleanID, QuoteItems.ContactID, Count(Nz([CleanID],0)) AS CountOfCleanID
FROM QuoteItems
GROUP BY QuoteItems.QuoteID, QuoteItems.CleanID, QuoteItems.ContactID
HAVING (((QuoteItems.QuoteID)=[Forms]![quotation]![QuoteID]) AND ((QuoteItems.CleanID)=1));

I used Nz() function instead of iif()

[vljones]

Thanks but no dice. I think the problem is in the fact that the query returns no result at all.

I am actually trying to populate a field on a sub report with a zero when the query that the sub report is based on returns no result.

Any other suggestions would be welcomed.

[hjozinovic]

Try using HasData property of your subreport for that

If(Me!SubRepot.Report.HasData=True,Then...,Else....)