Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/101076

From BetaArchive Wiki

Article ID: 101076

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q101076

Moderate: Requires basic macro, coding, and interoperability skills.

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

The following expressions can be used 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 (or NWIND.MDB in 1.x or 2.0)


  1. Open the sample database Northwind.mdb.
  2. Use the Report Wizard to create a Groups/Totals report based on the Products table.
  3. Select CategoryID and UnitPrice as the fields for your report.

    NOTE: In versions 1.x and 2.0, there is a space in Category ID and Unit Price.
  4. Group on CategoryID.
  5. In the report's Design view, select Sorting and Grouping from the View menu and make sure that the GroupFooter property for CategoryID is set to Yes.
  6. In the report's Design view, add an unbound text box in the CategoryID Footer section with the ControlSource property for the box set to the following expression:

          =Sum(IIF([Discontinued],1,0))
                            
  7. Add a second unbound text box with the ControlSource property for the box set to the following expression:

          =Sum(IIF([Discontinued],0,1))
                            
  8. 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.


Keywords: kbinfo KB101076