#ERROR in Sub-Report..HELP!

[Patricha]

I am in need of much help here.... kind of new to sub-reports.
My main report works fine and I have the sub report linked fine.

The problem is when there are no records in the subreport

Ok I have a sub-report that the record source is a sql

SELECT id,count(datemailed) as DateLetterMailedOut
FROM tblProduct
WHERE ContactType = "Mail"
GROUP BY id;

and when there are records the text box is populated.
what I want to be able to do is when there are no records found to show
0 (Zero) as the count

I tried putting in the control source of the text box

=iif(isnull([DateLetterMailed]),0,[DateLetterMailed])

then I tried
=iif(isnull([DateLetterMailed]),"0",[DateLetterMailed])

then I tried
=iif(isnull([datemailed]),0,[datemailed])

and I tried
=iif(isnull([datemailed]),"0",[datemailed])
and none of these worked.....

If anyone knows what I am doing wrong or has another solution, PLEASE HELP!!!

Thanks
Andrew
[sig][/sig]

 

[Robertd]

Hi Andrew,

Why not use the Count! in the iif statement
iif(count = 0,0,[DateLetterMailed])

HTH

[sig]<p>Robert Dwyer<br><a href=mailto:rdwyer@orion-online.com.au>rdwyer@orion-online.com.au</a><br>[/sig]

 

[Quehay]

Patricha,

I had to chuckle when I saw your examples--I tried to make a report textbox invisible if the value was null and went through all the same LOGICAL permutations that you did to no avail. What you want is to use Nz function:

=IIf(nz([field]),[field],&quot;0&quot



I got this from the help--look up Nz example (the examples for IIf are pretty skimpy--only one context given)

Hope this fixes it!