By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,680 Members | 2,139 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,680 IT Pros & Developers. It's quick & easy.

calculated text box

P: n/a
I have a field [result] and want to have a calculated field in
a report that counts it. It had control source set to "=count
([result]). As [result] can be also Null, I would like
it to count only values distinct from Null. I tried by
"Control Source=Count([result] Is Not Null), but it doesn't work, it still
counts all rows, even with Null.
How to set Control Source in the calcualted text box, in order to count only
values that are not null?

Thanks,

Zlatko

Jan 20 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
I tested to make sure I was remembering correctly since you're trying to
exclude Nulls, but my testing shows that Nulls aren't counted by Count().
The exception to this is Count(*) will return a count of records in the
recordset, but if you specify a field to count, then Nulls in that field are
ignored.

--
Wayne Morgan
MS Access MVP
"Zlatko Matić" <zl***********@sb.t-com.hr> wrote in message
news:dq**********@ss405.t-com.hr...
I have a field [result] and want to have a calculated field in
a report that counts it. It had control source set to "=count
([result]). As [result] can be also Null, I would like
it to count only values distinct from Null. I tried by
"Control Source=Count([result] Is Not Null), but it doesn't work, it still
counts all rows, even with Null.
How to set Control Source in the calcualted text box, in order to count
only
values that are not null?

Jan 20 '06 #2

P: n/a
Well, it surely counts both null and not null values, in my case. Maybe
because underlying query is pass-through query?
Anyway, I think I found a solution: "=Count(IIf(IsNull([result]);Null;1))".

Zlatko

"Wayne Morgan" <co***************************@hotmail.com> je napisao u
poruci interesnoj grupi:Vp*****************@newssvr27.news.prodigy.n et...
I tested to make sure I was remembering correctly since you're trying to
exclude Nulls, but my testing shows that Nulls aren't counted by Count().
The exception to this is Count(*) will return a count of records in the
recordset, but if you specify a field to count, then Nulls in that field
are ignored.

--
Wayne Morgan
MS Access MVP
"Zlatko Matić" <zl***********@sb.t-com.hr> wrote in message
news:dq**********@ss405.t-com.hr...
I have a field [result] and want to have a calculated field in
a report that counts it. It had control source set to "=count
([result]). As [result] can be also Null, I would like
it to count only values distinct from Null. I tried by
"Control Source=Count([result] Is Not Null), but it doesn't work, it
still
counts all rows, even with Null.
How to set Control Source in the calcualted text box, in order to count
only
values that are not null?


Jan 21 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.