Highlight duplicate in report

tucker61

Registered User.
Local time
Today, 03:00
Joined
Jan 13, 2008
Messages
344
Example on report

John, smith
Elaine, smith
Esther, jones.

Would like the surname smith highlighted.
 
Any ideas anyone, Would conditional formatting work ?
 
Conditional Formatting:
Code:
[COLOR=Blue][B]Expression Is[/B][/COLOR]:
DCount("*", "[COLOR=Red]TableName[/COLOR]", "[[COLOR=Red]Surname[/COLOR]] = " & [[COLOR=Red]Surname[/COLOR]] & "'")
Amend the red bits.
 
Could not get this to work, do i put this in the VBA code, if so i get a error, or do i put in the conditional formatting on the report?
 
In the same event where the code is. All you do is duplicate the code within the events.
 
I Have uploaded a small db, would like to highlight same first names, and same surnames.(Different Colours) I am working in access 2003, I have entered the code in the conditional formatting feild on the report, Hope this is correct. When i entered as Code on detail section, On format event, i get a compile error.
 

Attachments

I don't need to see your db, you can do it ;) Let's walk through the error. What is the exact error message? Copy and paste the entire code here (wrapped in --> code tags <-- )
 
this is how i have tried it in vba, and i get a compile error. Syntax error

[Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
DCount("*", "data", "[Surname] = " & [Surname] & "'")
End Sub/]

I have also entered as a expression on the conditional format function on the report page, and this does nothing.
 
Oops... that was my bad tucker61. The Expression in Conditional Formatting should be:
Code:
Nz(DCount("*", "data", "[Surname] = [COLOR=Red]'[/COLOR]" & [Surname] & "'"), 0) <> 0
The red bit was missing from the initial code.
 
this is now formatting all the surnames, not just the ones that are the same.
 
It should format surnames that appear more than once.

You also need a Default format too.
 
Ok, have a go and let me know. If you're still having no joy I will take a look at your db.
 
Managed to get it working, changed the end of the code from <>0 to >1. Works ok, but took 10 minutes to format a 12 page report.

Need to look at a quicker way to do it. But thanks for your help.
 
I knew it was going to be very slow but at least you now know how to construct a DCount() function well.

So to make things run much faster you can:

1. Create a totals query. Get a count per surname. You need three fields in this query: ID, Surname, Count(ID).
2. Save this query and join it to your report's query via the ID field
3. Use the Count field in the Conditional Formatting expression.
 
Hello. I am trying to get duplicate names to highlight in my report also. I can't get it to work. We have Access 2010 now. I am using this expression in the conditional fromatting;
Nz(DCount("*","Formal Training Information 2 Query","[Name]='"&[Name]&"'"),0)>1


Am i missing something? Thanks in advance.
 

Attachments

  • Capture.JPG
    Capture.JPG
    24.8 KB · Views: 167

Users who are viewing this thread

Back
Top Bottom