Duplicate in Report

  • Thread starter Thread starter nl
  • Start date Start date
Just how do you want to highlight them? I would think that a Find Duplicates
query would work to do what you want.
 
I would sort the report so that any duplicates appear together and then use
the "hide duplicates" property of the section. The duplicates would all show
in sequence and have only one instance of the field printed.
 
Someone else may have another idea, but I don't know of any simple way to
just highlight duplicate records.
 
I can see how it could be handled using a subquery that identifies the
duplicates and left joining that query to the original table and getting a
null value that could be used as a flag. Simple SQL example to identify which
records are or are not duplicates.

SELECT X.*
, IIF(Y.PrimaryKey is Null, False, True) as FlagBold
FROM SomeTable as X LEFT JOIN
(SELECT PrimaryKey
FROM SomeTable
WHERE SomeField in
(SELECT SomeField
FROM SomeTable
Group By SomeField
HAVING COUNT(SomeField) > 1)) as Y
ON X.PrimaryKey = Y.PrimaryKey


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Firstly it depends on what is meant by 'duplicate'. Does it mean the value
in one column is repeated in two or more rows? Does in mean the values in
all columns are repeated? Or des it mean the values in a subset of columns
are repeated?

Taking as an example a report based of contacts, in which 'duplication' means
having the same FirstName and LastName values, and the RecordSource of the
report is a query named qryContacts. The query can have parameters
referencing a control or controls on a form to restrict its results, but not
a simple parameter such as [Enter company:].

You can determine if there are two or more rows with identical FirstName and
LastName values by calling the DCount function in the report's detail
section's Format event procedure and changing a property such as each
control's ForeColor property if it returns a value greater than 1. The
following for instance would show the 'duplicates' in red, the rest in black:

Dim strCriteria As String
Dim ctrl As Control

strCriteria = "LastName = """ & Me.LastName & _
""" And FirstName = """ & Me.FirstName & """"

If DCount("*", "qryContacts", strCriteria) > 1 Then
For Each ctrl In Me.Section(acDetail).Controls
ctrl.ForeColor = vbRed
Next ctrl
Else
For Each ctrl In Me.Section(acDetail).Controls
ctrl.ForeColor = vbBlack
Next ctrl
End If

If the report is filtered by means of the WhereCondition argument of the
OpenReport method then you'd also have to pass the string expression used to
filter the report into it, which can be done as the OpenArgs argument of the
OpenReport method. This can then be included in the criterion for the DCount
function, e.g.

strCriteria = "LastName = """ & Me.LastName & _
""" And FirstName = """ & Me.FirstName & """"

If Not IsNull(Me.OpenArgs) Then
strCriteria = strCriteria & " And " & Me.OpenArgs
End If

Ken Sheridan
Stafford, England
I do want the duplicates records in the Report, but highlight them in the
Report.
Click to expand...
Click to expand...
 
Back
Top