Highlight duplicate in report

[tucker61]

Example on report

John, smith
Elaine, smith
Esther, jones.

Would like the surname smith highlighted.

 

[tucker61]

Any ideas anyone, Would conditional formatting work ?

 

[vbaInet]

Conditional Formatting:

[COLOR=Blue][B]Expression Is[/B][/COLOR]:
DCount("*", "[COLOR=Red]TableName[/COLOR]", "[[COLOR=Red]Surname[/COLOR]] = " & [[COLOR=Red]Surname[/COLOR]] & "'")

Amend the red bits.

 

[tucker61]

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?

 

[vbaInet]

In the same event where the code is. All you do is duplicate the code within the events.

 

[tucker61]

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.

 

[vbaInet]

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 <-- )

 

[tucker61]

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.

 

[vbaInet]

Oops... that was my bad tucker61. The Expression in Conditional Formatting should be:

Nz(DCount("*", "data", "[Surname] = [COLOR=Red]'[/COLOR]" & [Surname] & "'"), 0) <> 0

The red bit was missing from the initial code.

 

[tucker61]

this is now formatting all the surnames, not just the ones that are the same.

 

[vbaInet]

It should format surnames that appear more than once.

You also need a Default format too.

 

[tucker61]

Thanks, will have another go tomorrow.

 

[vbaInet]

Ok, have a go and let me know. If you're still having no joy I will take a look at your db.

 

[tucker61]

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.

 

[vbaInet]

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.

 

[fmargara]

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.