Prev: pilas
Next: dsum problem
From: johan on 20 May 2010 09:42
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

From: John Spencer on 20 May 2010 09:49
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

johan wrote:
> 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
From: Jerry Whittle on 20 May 2010 11:47
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
From: KenSheridan via AccessMonster.com on 20 May 2010 11:50
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/Forums.aspx/access/201005/1

 | 
Pages: 1
Prev: pilas
Next: dsum problem