Null records give Count - #error - is there a way to fix?

[Heidi]

I have a subreport that has a count for the number of names in it. I
reference this count in the main report and add it to the count there. My
problem is that sometimes the sub doens't have any data - i've tried IIf, Nz
and cant seem to come up with anything that will let me have the value be
zero if it's null instead of coming up #error. Does anyone know?

Thanks!

 

[Dirk Goldgar]

I have a subreport that has a count for the number of names in it. I
reference this count in the main report and add it to the count
there. My problem is that sometimes the sub doens't have any data -
i've tried IIf, Nz and cant seem to come up with anything that will
let me have the value be zero if it's null instead of coming up
#error. Does anyone know?

Keri Hardwick's nnz function, posted here ...

http://www.mvps.org/access/forms/frm0022.htm

.... should solve your problem.

 

[Heidi]

This is a report though so I can't get it to pass thru the code (tried
watches and breakpoints to check - doesn't even touch it). Doesn't fix the
problem..... tried it with the name of the count field....

 

[Dirk Goldgar]

This is a report though so I can't get it to pass thru the code (tried
watches and breakpoints to check - doesn't even touch it). Doesn't
fix the problem..... tried it with the name of the count field....

I don't follow you. I'm pretty sure that the functions works for
reports and subreports as well as it does for forms and subforms. See
if the discussion in this thread from 2001 makes it clearer how to use
it:


http://groups.google.com/group/micr...roup:*.*access*&rnum=7&hl=en#a3ca4328cb30dc7b

 

[Ofer]

Try this in the fields where you count the records, if there is no data
return it will return 0, else it should count the records

=IIf(HasData, count([Names]),0)

 

[Dirk Goldgar]

Try this in the fields where you count the records, if there is no
data return it will return 0, else it should count the records

=IIf(HasData, count([Names]),0)

Good idea, if the nnz() function can't be made to work for some reason.
I think Heidi wold have to refer to the property in the controlsource of
the control on the main report that refers to the "count" text box on
the subreport. Something along the lines of

=IIf([SubreportControlName].[Report].[HasData],
[SubreportControlName].[Report]![txtCountControl],
0)

 

[Heidi]

That worked great - thank you!!!

Try this in the fields where you count the records, if there is no data
return it will return 0, else it should count the records

=IIf(HasData, count([Names]),0)


--
The next line is only relevant to Microsoft''s web-based interface users.
If I answered your question, please mark it as an answer. It''s useful to
know that my answer was helpful
HTH, good luck

This is a report though so I can't get it to pass thru the code (tried
watches and breakpoints to check - doesn't even touch it). Doesn't fix the
problem..... tried it with the name of the count field....

 

[Heidi]

Perfect - this completed the question - thanks a bunch!!!

Try this in the fields where you count the records, if there is no
data return it will return 0, else it should count the records

=IIf(HasData, count([Names]),0)

Good idea, if the nnz() function can't be made to work for some reason.
I think Heidi wold have to refer to the property in the controlsource of
the control on the main report that refers to the "count" text box on
the subreport. Something along the lines of

=IIf([SubreportControlName].[Report].[HasData],
[SubreportControlName].[Report]![txtCountControl],
0)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)