Count of records with same values (1 Viewer)

[tbprog]

I have a report which shows a number of columns of information , the selection of data and its oreder are controled by the the clauses of an openreport method.
I can show the count of the over all number of records by a field = sum([field name]), however i would like to count the number of time a field value appears, eg

Name InvNo
AA 1
AA 3
BB 2

Total = 3 Total Names = 2

How do I get 2 even if the report is sorted on InvNo.

Thankyou

 

[chrismcbride]

tbprog
I am not sure that this is the most elegant way to do this, but it will work...
A 'SELECT DISTINCT' statement will retrieve one record for every distinct value within the named field. Once that is done you could place the return of the SELECT into a recordset and then the recordset's RecordCount property would hold the # of distinct records. This could be done in the format event of your report...
Good Luck
Chris

 

[tbprog]

Hi with your help and others I came up with this function which works, however I couldn't see how to use the 'SELECT DISTINCT' any ideas.

Public Function TB_Count(InSource, InField,InFilt)
Dim mydb As Database
Dim myrs1 As Recordset, myrs2 As Recordset
Dim oldid As String
Dim idcount As Long

idcount = 0

' set up recordset identical to report's recordset
Set mydb = CurrentDb()
Set myrs1 = mydb.OpenRecordset(InSource)

' set filter to report filter and sort on field to count
If InFilt = "" Then
Else
myrs1.Filter = InFilt
End If
myrs1.Sort = InField
Set myrs2 = myrs1.OpenRecordset

' work through recordset counting unique values
While myrs2.EOF = False
If myrs2(InField) <> oldid Then
idcount = idcount + 1
oldid = myrs2(InField)
End If
myrs2.MoveNext
Wend

TB_Count = idcount
End Function