If query returns no results...

D

Damon Heron

How do I put a zero in the textbox on the report? I have a query that gives
me an on hand beginning balance for a monthly report. It works fine, except
when there is a new product that was added during the month -in which case
the report shows a blank rather than zero.

Thanks in advance.

Damon
 
M

Michel Walsh

Hi,


The "blank" is probably a NULL value. We can change a NULL to another
value through Nz:

Nz( f1, f2 )


returns f1 unless f1 is null, then it returns f2.



Hoping it may help,
Vanderghast, Access MVP
 
D

Damon Heron

That doesn't work. I have played with nz, isnull, isempty, to no avail.
To further explain -My record source for the subreport is:

SELECT qryFTC1.SumOfGs, qryFTC1.TClassID
FROM qryFTC1;

This may return nothing, just as if you had a query with criteria that
didn't match anything.

Then, on my subreport is a textbox with control source SumOfGs. This is
where I want a zero if no results are returned by the query. Is this even
possible?

Damon
 
M

Michel Walsh

Hi,


If there is no record at all, you should handle the case in the NoData
event of the report. A possible action is maybe to then unhide ( turn
visible) a label (with a 0 ) strategically positioned.


Vanderghast, Access MVP
 
Top