ACC2000: Expressions to Count Yes, No, and Other Responses
ID: Q209505
|
The information in this article applies to:
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
SUMMARY
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.
MORE INFORMATION
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.mdb
- Open the sample database Northwind.mdb.
- Use the Report Wizard to create a Groups/Totals report based on the Products table.
- Select CategoryID and UnitPrice as the fields for your report.
- Group on CategoryID.
- In the report's Design view, click Sorting and Grouping on the View menu, and make sure that the GroupFooter property for CategoryID is set to Yes.
- 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. 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.
Additional query words:
inf
Keywords : kbdta RptTotal
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto