Report problem (NOT NULL)

[Atomsk]

I have a report based on a query (from another question I had asked here) - here's the SQL example:

SELECT Count(*) AS Cnt, GAPS.GAPID
FROM GAPS
WHERE *** (whatever criteria)
GROUP BY GAPS.GAPID;

Because of the "GROUP BY" statement, if no records match this criteria, then opening the query shows no records whatsoever, not even the perpetual floating "new" record. This becomes problematic for the report that I'm trying to base it on:

=Nz(IIf(IsNull([Cnt]),"0",Count([Cnt])))

When the corresponding query returns something (at least one record), the above statement works fine. However, if no records are returned, as in this case, then it displays #Error. I tried another statement:

=IIf(IsNull([Cnt]),"null","not null&quot

I expected it to return "null", but it actually returns "not null"....so it's null, but it's not NULL. What, if anything, can I change to get this working? Thanks for any help.

 

[psemianonymous]

There is no data in your report. If you want, you can go to the report events, click on the NoData event and create a subroutine. Then put "Cancel = True" in your subroutine, and it will cancel opening the report if there is no data.

There is no other way to properly view the report, in my opinion, if there is no data.

 

[Atomsk]

The thing is, I was planning to insert this into a main report as a subreport (one of many fields in a table). Is there at least some way to prevent it from displaying "#Error"? Blank is good too.

 

[psemianonymous]

Well there's something I never thought of.

Try, on the NoData() event, setting the textbox's control source to =""

That way you will hopefully get blank responses.


Again, I never even considered the possibility of subreports.

 

[Atomsk]

That did it. The main report takes a bit longer to load now, but at least it works. Thanks.