Skip to Content
0
Former Member
Jan 28, 2009 at 07:32 PM

CR XI: Cannot Display Null or Zero Values

325 Views

I summarize (count), based on Select Expert, a certain set of records from an Access database per month. Most months return a value (such as "2" or "4"), but some months, there are no records to count. Only the months that have records to count display in the report. I would like the months that do not have records to display as well, and show a "0" value. I have verified that 0 is not being suppressed.

FY2008

7/2008 5 (records)

FY2009

9/2008 2 (records)

10/2008 4 (records)

In this case, 7/2008, 9/2008, and 10/2008 had records, but 8/2008, 11/2008, and 12/2008 had none and therefore do not display. I would like 8/2008, 11/2008 and 12/2008 to display with "0".

I've grouped the records by month, obviously, and by day to distinguish between the date range in FY2008 and FY2009.

I've also tried a variety of formulas that have not changed the output, some of which I have seen on this forum. In the display string for the summary field, I have tried many variations of these formulas such as:

 Whileprintingrecords;
if isnull (DistinctCount ({Outage.OldOutageID}, {Outage.Outage Date}, "daily")) or 
(DistinctCount ({Outage.OldOutageID}, {Outage.Outage Date}, "daily")) =0 then
totext(GroupName ({Outage.Outage Date}, "monthly")) 

and

 IF ISNULL (DistinctCount ({Outage.OldOutageID}, {Outage.Outage Date}, "daily")) THEN
"0"
ELSE 
CSTR (DistinctCount ({Outage.OldOutageID}, {Outage.Outage Date}, "daily"))  

If anyone has suggestions, it would be greatly appreciated.