Count based on value... 3

[mmorancbt]

I am writing a report and I KNOW I should know this but...

I need to count total records in a given section (no problem) and then count total records that match a given criteria (a particular value).

All Visitors Those with follow up PCT %
10 8 80

I can produce the 10 and I can produce the percentage. How do I count a field that has a particular value or a value greater than x?

Thanks.

Matthew Moran

 

[TomCologne]

One way to do that:

DCount("[Field]","Qry_Or_TblName","[Criteria]='XYZ'")

as the report field's Control Source.

The correct expression depends on your data, see Help: DCount, Criteria Expressions.

TomCologne

 

[KenReay]

Hi

Why not, in the query on which you report is based nmake a calculated field FollowedUp:IIF(blnFollowed,1,0) and sum that ?

You need to use your own condition to denote a followup of course

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mmorancbt]

The last approach, the calculated field in the query, is the direction I was headed. I didn't know of there was a more graceful way.

Thanks for the input.

Matt

Matthew Moran

 

[dhookom]

I generally don't add a field into the query. If you want to count values from the detail section where a condition is true, then use a text box in a group or report footer with a control source like:
=Sum(Abs(_condition_))
For instance to count the number of females:
=Sum(Abs([Gender]="F"))
or to count the number of females hired since 1/1/2000:
=Sum(Abs([Gender]="F" AND [HireDate]>=#1/1/2000#))
or to find the sum of the salaries of females:
=Sum(Abs([Gender]="F") * [Salary])
The conditions evaluate to either true/-1 or false/0.

I rarely find a use for DCount() or DSum() in a report.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ElSteveO]

I've been trying to get dcount to work for hours until I read dhookom's post. I tried =sum(abs([field]=x)) and it worked great! Excellent advice. Star to dhookom!
Steve

 

[aruba]

How can I count the number of checkboxes checked in a report?
Thanks!!!

 

[dhookom]

=Sum(Abs([YourYesNoField]))

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]