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"
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.
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"
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.