By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,009 Members | 1,767 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,009 IT Pros & Developers. It's quick & easy.

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

P: 18
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.QuoteID, QuoteItems.CleanID, QuoteItems.ContactID, IIf(([CLeanID]) Is Null,0,Count([CleanID])) AS CountOfCleanID
FROM QuoteItems
GROUP BY QuoteItems.QuoteID, QuoteItems.CleanID, QuoteItems.ContactID
HAVING (((QuoteItems.QuoteID)=[Forms]![quotation]![QuoteID]) AND ((QuoteItems.CleanID)=1));
Aug 27 '08 #1
Share this Question
Share on Google+
3 Replies


100+
P: 167
Try this:

Expand|Select|Wrap|Line Numbers
  1. SELECT QuoteItems.QuoteID, QuoteItems.CleanID, QuoteItems.ContactID, Count(Nz([CleanID],0)) AS CountOfCleanID
  2. FROM QuoteItems
  3. GROUP BY QuoteItems.QuoteID, QuoteItems.CleanID, QuoteItems.ContactID
  4. HAVING (((QuoteItems.QuoteID)=[Forms]![quotation]![QuoteID]) AND ((QuoteItems.CleanID)=1));
I used Nz() function instead of iif()
Aug 27 '08 #2

P: 18
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.
Aug 27 '08 #3

100+
P: 167
Try using HasData property of your subreport for that

Expand|Select|Wrap|Line Numbers
  1. If(Me!SubRepot.Report.HasData=True,Then...,Else....)
Aug 27 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.