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.
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click
References on the
Tools menu in the Visual Basic Editor, and make sure that the
Microsoft DAO 3.6 Object Library check box is selected.
Examples
To create the function, follow these steps:
-
Open the sample database Northwind.mdb and create a new module.
-
Type the following lines in the Declarations section:
Dim strval As String
Dim sourcename As String
- Type the following procedure:
Function CountOccurrenceRecordset(strval As String, sourcename As _
String)
Dim db As DAO.Database, rs As DAO.Recordset, Strval_Count As Integer
Dim I As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset(sourcename, dbOpenDynaset)
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 - On the View menu, click the Immediate Window, 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 Immediate window.
If you want to search all fields in only one record, use the following
sample code. This example searches all fields in the first record only.
Function CountOccurrenceRecord(strval As String, sourcename As _
String)
Dim db As DAO.Database, rs As DAO.Recordset, Strval_Count As Integer
Dim I As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset(sourcename, dbOpenDynaset)
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