Count records in report which fit a criteria

  • Thread starter Thread starter PizzaBoy
  • Start date Start date
P

PizzaBoy

Hello Group. How would I create a text box which counts the number of
records where a text field contains a certain text string? (Example: I have
placed a text box in the report footer which has the expression
COUNT("[STATUS]='SIGNED WITH HRE'")

"Status" is the name of the field and 'SIGNED WITH HRE' is the value in the
field I am looking for. I have tried playing with different formats of the
same expression and it always returns the total count of all the records not
the records with the requested value. Thanks for any help.

(I am aware I can derive this information from a Totals Query, but I would
like to have a text box for each value the "Status" field contains as well
as a couple of other fields contained in the report so I would have to
create several queries"
 
PizzaBoy said:
Hello Group. How would I create a text box which counts the number of
records where a text field contains a certain text string? (Example: I have
placed a text box in the report footer which has the expression
COUNT("[STATUS]='SIGNED WITH HRE'")

"Status" is the name of the field and 'SIGNED WITH HRE' is the value in the
field I am looking for. I have tried playing with different formats of the
same expression and it always returns the total count of all the records not
the records with the requested value. Thanks for any help.

(I am aware I can derive this information from a Totals Query, but I would
like to have a text box for each value the "Status" field contains as well
as a couple of other fields contained in the report so I would have to
create several queries"
Click to expand...


The key to clear thinking on this kind of thing is that all
the Aggregate functions (Count, Sum, etc) ignore Null
values.

Since Count only counts the non-null values, the expression
you used will be True or False (neither of which is Null)
will count every entry.

You could use this:
=Count(IIf([STATUS]='SIGNED WITH HRE', 1, Null))
Note that it doesn't matter what you use where I have a 1.

A more obscure, but somewhat faster way to get the same
result is:
=Abs(Sum([STATUS]='SIGNED WITH HRE'))
which relies on Access using -1 for True and 0 for False.
 
Thank you.

Marshall Barton said:
PizzaBoy said:
Hello Group. How would I create a text box which counts the number of
records where a text field contains a certain text string? (Example: I
have
placed a text box in the report footer which has the expression
COUNT("[STATUS]='SIGNED WITH HRE'")

"Status" is the name of the field and 'SIGNED WITH HRE' is the value in
the
field I am looking for. I have tried playing with different formats of the
same expression and it always returns the total count of all the records
not
the records with the requested value. Thanks for any help.

(I am aware I can derive this information from a Totals Query, but I would
like to have a text box for each value the "Status" field contains as well
as a couple of other fields contained in the report so I would have to
create several queries"
Click to expand...


The key to clear thinking on this kind of thing is that all
the Aggregate functions (Count, Sum, etc) ignore Null
values.

Since Count only counts the non-null values, the expression
you used will be True or False (neither of which is Null)
will count every entry.

You could use this:
=Count(IIf([STATUS]='SIGNED WITH HRE', 1, Null))
Note that it doesn't matter what you use where I have a 1.

A more obscure, but somewhat faster way to get the same
result is:
=Abs(Sum([STATUS]='SIGNED WITH HRE'))
which relies on Access using -1 for True and 0 for False.
Click to expand...
 
Back
Top