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

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æ" <zlatko.matic1@sb.t-com.hr> wrote in message
news:dqqgeu$j8b$1@ss405.t-com.hr...[color=blue]
>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?[/color]

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" <comprev_gothroughthenewsgroup@hotmail.com> je napisao u
poruci interesnoj grupi:Vp4Af.15166$Yu.4966@newssvr27.news.prodigy.n et...[color=blue]
>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æ" <zlatko.matic1@sb.t-com.hr> wrote in message
> news:dqqgeu$j8b$1@ss405.t-com.hr...[color=green]
>>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?[/color]
>
>[/color]