Eric:
Using the Count function will count rows not distinct values of the names.
The following code in a report's module will do this:
''''module starts''''
Option Compare Database
Option Explicit
Dim strNameList As String
Dim intNameCount As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If InStr(strNameList, "~" & Me.LastName) = 0 Then
strNameList = strNameList & "~" & Me.LastName
intNameCount = intNameCount + 1
End If
End Sub
Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
Me.txtNameCount = intNameCount
End Sub
''''module ends''''
where LastName is a control in the report's detail section containing the
names to be counted and txtNameCount is an unbound control in the report
footer in which the count is displayed. It works by creating a list of
distinct name values delimited by the tilde character (on the assumption that
no names will contain a tilde). If a name is not yet present in the list its
added and the count is incremented by 1. I've successfully used this
approach with a report of over 7000 rows involving over 200 distinct values.
Ken Sheridan
Stafford, England
