Microsoft KB Archive/142227

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

Article ID: 142227

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q142227



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:  Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0) and create a new module.  Type the following lines in the Declarations section: Dim strval As String Dim sourcename As String   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  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: kbhowto kbprogramming KB142227

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.