?
Solved

MS Access Reports

Posted on 2005-04-21
5
Medium Priority
?
349 Views
Last Modified: 2013-11-28
MS Access Report shows Blank when there's no data in the table (when count is 0).  Is there any workaround / solution to this problem?
0
Comment
Question by:picasothakkar
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 13834079
Hi picasothakkar,
Reports have a OnNoData property.
You can add code to the corresponding event procedure to display a 'No Data' msgbox and the close the report.
(I assume this is the sort of thing you are after)


Pete
0
 

Author Comment

by:picasothakkar
ID: 13834990
Thanks Pete...

Actually i am fetching the data thru many queries in a single report.
If there is no data for one of the queries the report doesnot show any data even though other queries hava data.

Also I need to show as zero if no records are returned by query....

One of the query is as follows:

SELECT Count(qsel_SLA_New.ReqNumber) AS [Total Count], qsel_SLA_New.Priority AS PRIORITY
FROM qsel_SLA_New
WHERE (((qsel_SLA_New.Group1)=[Forms]![Report Selector]![cboGroup]) AND ((qsel_SLA_New.Priority)="SEV 2"))
GROUP BY qsel_SLA_New.Priority;

If there is no record i need to show as zero....

0
 
LVL 77

Expert Comment

by:peter57r
ID: 13835202
I'm afraid I don't understand what you are describing.

Are you saying you have a main report and a series of subreports?
If not where are these queries running?

Pete
0
 

Author Comment

by:picasothakkar
ID: 13835715
Hi Pete,

I have a main query named as "qsel_SLA_New" which is fetching data from many tables is shown below.

SELECT call_req.persid, call_req.ref_num AS ReqNumber, call_req.summary, IIf([active_flag]=1,"Yes",IIf([Active_flag]=0,"No")) AS Active, CvrtFromUnixTime([open_date]) AS Date_Opened, CVDate(IIf(IsNull([call_req].[last_mod_dt]),Null,CvrtFromUnixTime([call_req].[last_mod_dt]))) AS Date_Last_Modified, CVDate(IIf(IsNull([close_date]),Null,CvrtFromUnixTime([close_date]))) AS Date_Closed, IIf(IsNull([ctct_1].[c_last_name]),"Unassigned",[ctct_1].[c_last_name] & ", " & [ctct_1].[c_first_name]) AS Customer, ctct_2.c_last_name AS Group1, IIf(IsNull([ctct].[c_last_name]),"Unassigned",[ctct].[c_last_name] & ", " & [ctct].[c_first_name]) AS Assigned, IIf(IsNull([int_org].[iorg_name]),"Unassigned",[int_org].[iorg_name]) AS Organization, IIf([sla_violation]=0,"No",IIf([sla_violation]>=1,"Yes",IIf(IsNull([sla_violation]),"No"))) AS [Sla Violated], srv_desc.sym AS [Service Type], call_req.open_date, pri.sym AS Priority
FROM (pri RIGHT JOIN ((((call_req LEFT JOIN ctct ON call_req.assignee = ctct.id) LEFT JOIN int_org ON call_req.group_id = int_org.id) LEFT JOIN srv_desc ON call_req.support_lev = srv_desc.code) LEFT JOIN ctct AS ctct_1 ON call_req.customer = ctct_1.id) ON pri.enum = call_req.priority) INNER JOIN ctct AS ctct_2 ON call_req.group_id = ctct_2.id
WHERE (((call_req.open_date)>=[Forms]![Report Selector]![txtUnixStart] And (call_req.open_date)<[Forms]![Report Selector]![txtUnixEnd]));


Now from this query, I am creating 3 other queries which counts the no. of records based on different conditions, one example is :

SELECT Count(qsel_SLA_New.ReqNumber) AS [Total Count], qsel_SLA_New.Priority AS PRIORITY
FROM qsel_SLA_New
WHERE (((qsel_SLA_New.Group1)=[Forms]![Report Selector]![cboGroup]) AND ((qsel_SLA_New.Priority)="SEV 2"))
GROUP BY qsel_SLA_New.Priority;

This doesn't show anything if there is no record while I need to show output as 0 records.

From output of these 3 queries, I am creating a summary report.

If u can give me your email address, I can send the qyeries and report output as attachments.

Thanks!

0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 13836030
There is redundant code in the query:
Use
SELECT Count(qsel_SLA_New.ReqNumber) AS [Total Count], "Sev 2" AS PRIORITY
FROM qsel_SLA_New
WHERE (((qsel_SLA_New.Group1)=[Forms]![Report Selector]![cboGroup]) AND ((qsel_SLA_New.Priority)="SEV 2"))

and you will get a record with a zero count.

Pete

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question