ACC: How to Count Values in Record or Recordset Across Fields

Last reviewed: August 28, 1997
Article ID: Q142227
The information in this article applies to:
  • Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multi-user skills.

This article defines a sample user-defined Visual Basic for Applications routine that you can use to count the occurrence of a specific field value in a record or recordset.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

The following function is designed for text fields, but it can be modified to work with any data type and assumes that all fields are of the same data type. This function is designed to work on a single table or query; it accepts two arguments:

  • Strval, a string used to identify the value or text
  • Sourcename, the name of the table or query to search

Examples

To create the function, follow these steps:

  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0) and create a new module.

  2. Type the following lines in the Declarations section:

          Dim strval As String
          Dim sourcename As String
    

  3. Type one of the following procedures:

    Microsoft Access Version 7.0 and 97:

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

        Function CountOccurrenceRecordset(strval As String, sourcename As _
          String)
          Dim db As DATABASE, rs As Recordset, Strval_Count As Integer
          Dim I As Integer
          Set db = CurrentDb
          Set rs = db.OpenRecordset(sourcename, DB_OPEN_DYNASET)
          rs.MoveFirst
          Strval_Count = 0
          Do Until rs.EOF
    
             For I = 0 To rs.Fields.Count - 2
                If TypeName(rs.Fields(I).Value) <> "Byte()" Then
             ' In version 7.0, the OLE data type returns a data type error 13
             ' on a comparison. This is to exclude that field from the search.
                   If rs.Fields(I).Value = strval Then
                      Strval_Count = Strval_Count + 1
                   End If
                End If
             Next I
             rs.MoveNext
          Loop
          MsgBox "Count of " & strval & " found = " & Strval_Count
          CountOccurrenceRecordset = Strval_Count
    
        End Function
    
       Microsoft Access Version 2.0:
    
       NOTE: In the following sample code, an underscore (_) at the end of a
       line is used as a line-continuation character. Remove the underscore
       from the end of the line when re-creating this code in Access Basic.
    
        Function CountOccurrenceRecordset (strval As String, sourcename As _
          String)
          Dim db As Database, rs As Recordset, Strval_Count As Integer
    
          Set db = dbengine(0)(0)
          Set rs = db.OpenRecordset(sourcename, DB_OPEN_DYNASET)
          rs.MoveFirst      'Move to first record in recordset.
          Strval_Count = 0  'Set strval_Count variable to zero.
          Do Until rs.EOF   'Loop until end of recordset(EOF).
             For i = 0 To rs.fields.Count - 1       'Loop through each field.
                If rs.fields(i) = strval Then       'Do next line if the field
                                                    'equals string variable.
                   Strval_Count = Strval_Count + 1  'Add one to string
                                                    'value.
                End If
             Next i
             rs.MoveNext    'Move to next record.
          Loop
          MsgBox "Count of " & strval & " found = " & Strval_Count
          'Display count of sting in message box.
          CountOccurrenceRecordset = Strval_Count
        End Function
    
    

  4. On the View menu, click Debug window (or Immediate window in version 2.0), type the following line, and then press ENTER:

          ? CountOccurrenceRecordset("Robert","Employees")
    

    Note that the message box returns "Count of Robert found = 1."

To demonstrate that more than one record can be found, open the Employees table and change another employee's first name to Robert, commit the record, and close the table. Then rerun the function in the Debug window (or Immediate window in version 2.0).

If you want to search all fields in only one record, use the following sample code for your version of Microsoft Access. This example searches all fields in the first record only.

Microsoft Access Versions 7.0 and 97:

   Function CountOccurrenceRecord(strval As String, sourcename As _
      String)
      Dim db As DATABASE, rs As Recordset, Strval_Count As Integer
      Dim I As Integer
      Set db = CurrentDb
      Set rs = db.OpenRecordset(sourcename, DB_OPEN_DYNASET)
      rs.MoveFirst
      Strval_Count = 0
      For I = 0 To rs.Fields.Count - 2
         If TypeName(rs.Fields(I).Value) <> "Byte()" Then
            If rs.Fields(I).Value = strval Then
               Strval_Count = Strval_Count + 1
            End If
         End If
      Next I
      MsgBox "Count of " & strval & " found = " & Strval_Count
      CountOccurrenceRecord = Strval_Count
   End Function

Microsoft Access Version 2.0:

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

   Function CountOccurrenceRecord (strval As String, sourcename As _
      String)
      Dim db As Database, rs As Recordset, Strval_Count As Integer

      Set db = dbengine(0)(0)
      Set rs = db.OpenRecordset(sourcename, DB_OPEN_DYNASET)
      rs.MoveFirst
      Strval_Count = 0
      For i = 0 To rs.fields.Count - 1
         If rs.fields(i) = strval Then
            Strval_Count = Strval_Count + 1
         End If
      Next i
      MsgBox "Count of " & strval & " found = " & Strval_Count
      CountOccurrenceRecord = Strval_Count
   End Function


Additional query words: how to
Keywords : JetRS kbprg PgmHowTo MdlDao
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: August 28, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.