Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 209505 - Last Review: March 29, 2007 - Revision: 3.1
Description of the expressions to count yes, no, and other responses in Access
This article was previously published under Q209505
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.
This article lists sample expressions that you can use to count the
occurrences of Yes, No, or Null in a field with a Yes/No data type.
You can use the following expressions in a report footer to count the
occurrences of Yes, No, or Null in a field named YesNoField with a data
type of Yes/No:
Expression Sums What
--------------------------------------------
=Sum(IIF([YesNoField],1,0)) Yes
=Sum(IIF([YesNoField],0,1)) No
=Sum(IIF(Not[YesNoField],1,0)) No
=Sum(IIF(IsNull[YesNoField],1,0)) Null
You can also create a related expression to count a specific value in
a field. For example, the following sample expression counts all
occurrences of the value 3 in a field called MyField.
=Sum(IIF([MyField]=3,1,0))
Example Using Sample Database Northwind
- Open the sample database Northwind in Access.
- Use the Report Wizard to create a report based on the Products table.
- Select CategoryID and UnitPrice as the fields for the report.
- Group on CategoryID.
- In the design view of the report, click Sorting and Grouping on the View menu, and make sure that the GroupFooter property for CategoryID is set to Yes.
Note In Access 2007, in the design view of the report, on the Design tab, click Group & Sort in the Grouping & Totals group, and make sure that the with a footer section property for CategoryID is selected. - Add an unbound text box in the CategoryID footer section with the ControlSource property for the text box set to the following expression:
=Sum(IIF([Discontinued],1,0))
- Add a second unbound text box with the ControlSource property for the text box set to the following expression:
=Sum(IIF([Discontinued],0,1))
- On the File menu, click Print Preview.
In Access 2007, click Microsoft Office Button, point to Print, and then click Print Preview.
Notice that the first expression will count the number of products within each category that have the Discontinued field set to Yes. The second expression will count the number of products within each category that have the Discontinued field set to No.
APPLIES TO
- Microsoft Access 2000 Standard Edition
- Microsoft Access 2002 Standard Edition
- Microsoft Office Access 2003
- Microsoft Office Access 2007
| kbexpertiseinter kbinfo kbhowto KB209505 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate