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

MORE INFORMATION

The following function is designed for Text fields, but you can modify it to work with any data type. It assumes that all fields are of the same data type. This function is designed to work on a single table or query. It accepts the following 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