473,325 Members | 2,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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

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
3 1697
hjozinovic
167 100+
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
vljones
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
hjozinovic
167 100+
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

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

Similar topics

5
by: Ken Lindner | last post by:
We have a report based on a query that is called from a form. Nothing too special here. The query has 3 coulmns that are populated directly from values entered into entry fields on the form...
1
by: Yisroel Markov | last post by:
Greetings, In Access 97 I have a subreport with two labels and a control. The record source for the control is a simple SQL query referring to another query: SELECT DISTINCTROW...
1
by: Craig M | last post by:
Hi, I've created a query that has all calculations needed, and made a report based on this. I have then made a form based on this query. What I would like to do is print only the current...
5
by: Randy Harris | last post by:
How can I report an average of non zero values? If the values are: 5, 0, 6, 0, 4 I would like the result 5 (15 / 3), not 3 (15 / 5) Thanks for any help...
15
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows...
2
by: amith.srinivas | last post by:
Hi all, From a word macro in VBA, I am trying to create a report in access DB. The report is based on a query with a parameter. I am using Set rpt = Application.CreateReport rpt.RecordSource =...
6
by: tizmagik | last post by:
I am having a lot of difficulty generating a CrossTab Query based report. I have looked online for several tutorials and whatnot but I have not been able to really find what I'm looking for, nor...
5
by: Wired Hosting News | last post by:
I tried to be breif and give a scenario so as not to be overlooked because it was soooo long. Let me give you real world. I am a manufacturer of goods and produce 11 items that are distributed...
14
by: akmaRudiliyn | last post by:
Hi Everybody.. This is my firstime in creating report using microsoft access. Actually i need to build a report based on a database call Table1. I only use 2 column of databse to put in my...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.