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

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 shows you how to create 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 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.

      Dim strval As String
      Dim sourcename As String

   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

   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

Keywords          : kbprg MdlDao 
Version           : 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998