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

• 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."

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

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