Counting number of records based on criteria

Hello,

(Try again to get the question in the google-groups. Perhaps not
pushed on the send button).

For counting records as expression in a report in ms.access I'm using
=count(*) which shows all records.

Now I also want to know how many of them has registered a specific
data in a specific field.

I'm trying to use =count([Field1]="999") to count how many of the
selection reported has registered data 999.
This option isn't work. Can somebody give me the correct solution.

thanks,
Johan

The 999 needs to go in the criteria for the query driving the report or the
filter for the report.

You could also group by Field1 if you want to see the count for the various
data in Field1.

You could also use DCount in a text field which would look something like:

=DCount("[Field1]", "TheTableName", "[Field1] = '999'")

Note that DCount, or any of the aggregate functions starting with "D", can
be very slow when used like this.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"johan" wrote:

Hello,

(Try again to get the question in the google-groups. Perhaps not
pushed on the send button).

For counting records as expression in a report in ms.access I'm using
=count(*) which shows all records.

Now I also want to know how many of them has registered a specific
data in a specific field.

I'm trying to use =count([Field1]="999") to count how many of the
selection reported has registered data 999.
This option isn't work. Can somebody give me the correct solution.

thanks,
Johan

Either of these should do it:

=Sum(IIf([Field1]="999",1,0))

or:

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

Ken Sheridan
Stafford, England

johan wrote:
Hello,

(Try again to get the question in the google-groups. Perhaps not
pushed on the send button).

For counting records as expression in a report in ms.access I'm using
=count(*) which shows all records.

Now I also want to know how many of them has registered a specific
data in a specific field.

I'm trying to use =count([Field1]="999") to count how many of the
selection reported has registered data 999.
This option isn't work. Can somebody give me the correct solution.

thanks,
Johan

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1