SJM:
Try this out for size. It assumes that you have a table or a query as the
record source for the report and that [PropertyID] is the unique identifier
of the property and that [PropertyID] is an output column of the record
source. Create a field named txtUniqueRecs in the report footer section,
then copy this code into the On Print event.
Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
On Error Resume Next
Dim rst As DAO.Recordset
Dim strRecSource As String
Dim strSQL As String
strRecSource = Me.RecordSource
strSQL = "SELECT DISTINCT PropertyID FROM (" & strRecSource & "); "
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.BOF And rst.EOF Then
'there's no data,
'return 0
Me!txtUniqueRecs = 0
Else
rst.MoveLast
Me!txtUniqueRecs = rst.RecordCount
End If
Set rst = Nothing
End Sub
Add whatever error routine you normally use.
Good luck,
Ed
"SJM" <no****@ms.com> wrote in message
news:Ny****************@nnrp1.ozemail.com.au...
I have a report that displays records of real estate properties. It is
possible for each property to appear a number of times for various
reasons. Each record however is unique. What I would like to do is display the
total of the number of unique properties in the report footer, not just a count
of the number of records. I have experimented with grouping on the property
field and using running sums but to no avail. I have also tried to
determine the unique property count by using a recordset based on the query
underlying the report in the report footer on format event, but this would not work
as it complained of not having the required parameter which did not make
sense. Maybe I could save the underlying data to a table and base the report on
that and then get a recordcount on a recordset based on the table to fill
in the unique property count field in the report. Should it be an easy thing
to display a 'unique count' in a report footer?
