Count distinct records on a report (1 Viewer)

spet

Registered User.
Local time
Today, 08:50
Joined
Oct 5, 2018
Messages
38
Hello!


I have created a report that contains employee records. An employee can show up twice on the report if they have regular and overtime.


Example:
Emp No Hours Type

100 40 Reg
100 2 OT
102 40 Reg


At the bottom of the report, I would like to display how many employees are on the report, not the record count. Is there a way to do a Count(Distinct(EmpNo))???


Thank you in advance!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:50
Joined
May 7, 2009
Messages
19,169
you can have an Unbound textbox at the bottom (Page footer or Report Footer).
add code to Load event of your report to count:
Code:
Private Sub Report_Load()
    Dim sSQL As String
    If InStr(Me.RecordSource, "SELECT") > 0 Then
        sSQL = "SELECT COUNT('1') FROM (SELECT DISTINCT T1.EMPNO FROM (" & Me.RecordSource & ") AS T1)"
    Else
        sSQL = "SELECT COUNT('1') FROM (SELECT DISTINCT T1.EMPNO FROM " & Me.RecordSource & " AS T1)"
    End If
    With CurrentDb.OpenRecordset(sSQL)
        Me.UnboundTextbox = .RecordCount
    End With
End Sub
 

Users who are viewing this thread

Top Bottom