Counting number of records based on criteria

[johan]

Hello,

In a report I'm using in a reportfield the expression =Count(*) which
gives me the total number of records showed in this report. I'm also
want to know from a specific datafield in this report how many of them
are showed.

Something like =Count([Field1]="999")
In words.... count the number of fields where the registered data is
999

When I'm using it as described above, then the output gave me the same
number as with =Count(*)

What's wrong ?
Please help me out.

regards,
Johan

 

[Alexander Achenbach]

Hello Johan,

the Count() function is only for counting not-null-values (or complete lines
with *). It is not designed to be used with an condition. Your condition
evaluates something (it doesn't matter if the result is true or false) which
is counted.

Either use the DCount function instead which is independent(!) to your
report
=DCount("some field"; "table/query";"[Field1]=999") but note that if you are
using groups or filterted data in your report you should add these creteria
to the third parameter.

Or you use a hidden helper field in your details section:
Name: Helper1
ControlSource: =CLng([Field1]=999)*-1
Visible=False

In the report footer (or any group footer) you then can refer to the count
by using =Sum([Helper1]).

Note that Helper1 evaluates your condition and converts the logical
True/False to numerical (CLng) -1/0. These values then must be sumarized.

Yours Alexander

 

[John Spencer]

You can do what you want using
=Abs(Sum([Field1]="999"))

Or

=Count(IIF([Field1]="999",1,Null))

The expression [Field1]="999" will return True or False. True is equal to -1
and false is equal to zero. So the sum of those values is equal to a negative
count of the records where the condition is met. Abs removes the negative sign.

Count counts the presence of any value that is NOT null, the the IIF function
returns null when the expression is false. Again you get an accurate count of
the trues since 1 is a non-null value. You could put "X" or 2000 in as the
second argument to the IIF and get the same result.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County