Find Duplicate Record Issue....U

[autiger58]

I have acquired what seems to me to be a unique and perplexing issue.

Access's built in Find Duplicates Query finds records based on blank field values. For Example lets say I have a DB with fields A-Z. I tell the Find Duplicates Query that I want to find Duplicate records using field C D and E. All three are numbers stored as text with field C being a unique LOCATION number (which would have MANY duplicates), field D (which would have many duplicate) and E (which would have many duplicate) but when you use all three fields there should only be ONE record with those three fields combined.

For Example

C D E
4000 100 101
4000 100 102
4000 100 103
4100 100 101


4100 100 102


When I run the duplicate query, it tells me that I have duplicate records when D AND E are BLANK. Where the problem is occurring is that I have 5000 records of which 4000 have values in fields D and E and 1000 that don't for various reasons. The query is telling me that all of the 1000 records with blank D and E fields are duplicates.

So here's what is making this even more perplexing to me....

Two weeks ago, everything worked perfectly. The query would NOT identify records with blank fields in D a E as duplicates. Then I acquired some data from an outside source and added it to my existing DB making sure that every field matched in data type. It is this NEW data that is showing as duplicate but none of the original data with blank D and E fields.

My first thought is that there is some kind of hidden symbol or value in the field that I can not see but if something is there, I can't find it. I exported the entire DB into excel, run the find duplicate from there using the same fields and it finds NO duplicates.

To hopefully remove any thing that I am not seeing in the table, I created a new DB and created just the Structure only in a new table and then copied and pasted just the data into that new table...run the dups query...same result. I then remove the new data that I acquired and the dup query work perfectly.

Does anyone have any idea of what might be going on here?

Thanks,

Michael

[aytee111]

How are you adding the new data to the table? Maybe use an append query and only append data that is not null. Test the new data, see if the blank fields are null or "".

[autiger58]

Thanks for the reply. When I first started having issues I notices the "" in the data type. I deleted it and are still having problems. Curious, what does the "" mean in the data type and why would it be there?

I think I may have found a work around that fix's my problem which I will post back in a bit.