Solved: Problem with the 'Find Duplicates Query Wizard' in Access 2007

[lazyme]

Hi,

I am having a problem with the 'Find Duplicates Query Wizard' in Access 2007. The wizard does not seem to be matching blank fields as duplicates.

For example

"NAME", "DOB", "MARKS", "GRADE"
"John Smith", "9/1/1984", "92", ""
"John Smith", "9/1/1984", "92", ""

is not considered as a duplicate whereas


For example

"NAME", "DOB", "MARKS", "GRADE"
"John Smith", "9/1/1984", "92", "A"
"John Smith", "9/1/1984", "92", "A"

is considered a duplicate.

Why does this happen? And is there a way to get around this problem?

[austenr]

Unless I am wrong, Access does dot consider a blank field when checking for dups. Because there is nothing in it, no value to compare.

I noticed that the blank field has no Field name if indeed that is what they are.

Your out of luck as it appears the routine is running correctly.

[lazyme]

Hi austenr,

In my example the blank fields are under the Grade field name. If the blank fields are not considered then the two values should be treated as duplicates as all the remaining fields match, right?

However, access does not treat them as duplicates..

[austenr]

But my answer to you is still that nothing compared to nothing in the eyes of Access is not a duplicate. It sees that there is no data so it is ignored. There must be something in that field to compare it to.

Test it by making a blank table and running the query wizard. See what result you get.

[geekgirlau]

Of course a simple solution might be using nz. If your query is evaluating nz(Grade, "ZZZ") then you'll find your duplicates even if they're blank.

[lazyme]

Thanks geekgirl. This is a great workaround for what I needed :)