Highlighting duplicates only when a 2nd column is different (1 Viewer)

[Danick]

I'm trying to use conditional formatting to highlight duplicate cells but only when a corresponding cell in the same record is different than the 1st one. Here's a picture of what I'm trying to do.

Notice that the SN field gets highlighted for duplicate SNs, but ONLY when the corresponding date fields is different. I'm thinking it's gotta be some for of DCount that looks like this, but can't figure out how to add the second field condition. Note that all fields are text fields.

DCount("*", "NameOfTable", "[NameOfField]=" & Chr(34) & [NameOfField] & Chr(34)) > 1

 

[theDBguy]

What I would probably do is create a separate query with DISTINCT values, so you can check if there are duplicate SNs with different Dates.

 

[June7]

As theDBguy says, you need to know how many DISTINCT date values each SN has. So build query object and do DCount() on that dataset.

SELECT DISTINCT SN, [Date] FROM table;

DCount("*", "queryname", "SN=" & [SN]) > 1

Why are fields text type? If SN is text, why is it aligned right like number type?

BTY, Date is a reserved word and should not use reserved words as names.

 

[Danick]

As theDBguy says, you need to know how many DISTINCT date values each SN has. So build query object and do DCount() on that dataset.

SELECT DISTINCT SN, [Date] FROM table;

DCount("*", "queryname", "SN=" & [SN]) > 1

Why are fields text type? If SN is text, why is it aligned right like number type?

BTY, Date is a reserved word and should not use reserved words as names.

Thank you all very much. That worked very well. Just had to make a couple of adjustments as the fields are all text fields.

DCount("*","qryDuplicateSNs","[Serial Number]=" & Chr(34) & [Serial Number] & Chr(34))>1

As to the why they are all text fields? This is a spreadsheet that is imported from SAP and ALL the fields are text fields. They look like numbers in the screenshot because I just made a simple table in Excel of what I wanted the output to look like and pasted it here. Also the Date Field isn't really called [Date], but a rather long obscure date field name, so I just called it that way for simplicity.

Thanks again for your help and how quickly you responded.

 

[June7]

You can use embedded apostrophe instead of concatenating Chr(34)

DCount("*","qryDuplicateSNs","[Serial Number]='" & [Serial Number] & "'")>1