"Find Duplicates" Query

F

FJquestioner

I have a query to find duplicate invoice numbers. I use the access find
duplicate wizard query as follows:

SELECT Invoices.AssignorInvoiceNumber
FROM Invoices
WHERE (((Invoices.AssignorInvoiceNumber) In (SELECT [AssignorInvoiceNumber]
FROM [Invoices] As Tmp GROUP BY [AssignorInvoiceNumber] HAVING Count(*)>1)))
ORDER BY Invoices.AssignorInvoiceNumber;

It works fine however I have a related macro wherein a form is closed only
if there are no duplicates found in the above query. I set the form to close
if the AssignorInvoiceNumber in the query is Null. However, when there are
no duplicates the query returns blank cells that seem to be neither Null nor
blank fields like " " so the condition in my macro won't work.

In essence, what condition do I write in a macro such that the macro action
occurs when the query above reveals no duplicates?

I'm stumped.

Thanks very much in advance.
 
G

Gary Walter

"FJquestioner"wrote:
I have a query to find duplicate invoice numbers. I use the access find
duplicate wizard query as follows:

SELECT Invoices.AssignorInvoiceNumber
FROM Invoices
WHERE (((Invoices.AssignorInvoiceNumber) In (SELECT [AssignorInvoiceNumber]
FROM [Invoices] As Tmp GROUP BY [AssignorInvoiceNumber] HAVING Count(*)>1)))
ORDER BY Invoices.AssignorInvoiceNumber;

It works fine however I have a related macro wherein a form is closed only
if there are no duplicates found in the above query. I set the form to close
if the AssignorInvoiceNumber in the query is Null. However, when there are
no duplicates the query returns blank cells that seem to be neither Null nor
blank fields like " " so the condition in my macro won't work.

In essence, what condition do I write in a macro such that the macro action
occurs when the query above reveals no duplicates?
I don't use macros, but the test I'd probably use
would involve the domain function DCount
(assuming query above were named "qryDup")

If DCount("*","qryDup") = 0 Then
'close form
Else
'do something else (or nothing)
End If
 
F

FJquestioner

Thanks Gary. It worked perfectly.

Gary Walter said:
"FJquestioner"wrote:
I have a query to find duplicate invoice numbers. I use the access find
duplicate wizard query as follows:

SELECT Invoices.AssignorInvoiceNumber
FROM Invoices
WHERE (((Invoices.AssignorInvoiceNumber) In (SELECT [AssignorInvoiceNumber]
FROM [Invoices] As Tmp GROUP BY [AssignorInvoiceNumber] HAVING Count(*)>1)))
ORDER BY Invoices.AssignorInvoiceNumber;

It works fine however I have a related macro wherein a form is closed only
if there are no duplicates found in the above query. I set the form to close
if the AssignorInvoiceNumber in the query is Null. However, when there are
no duplicates the query returns blank cells that seem to be neither Null nor
blank fields like " " so the condition in my macro won't work.

In essence, what condition do I write in a macro such that the macro action
occurs when the query above reveals no duplicates?
I don't use macros, but the test I'd probably use
would involve the domain function DCount
(assuming query above were named "qryDup")

If DCount("*","qryDup") = 0 Then
'close form
Else
'do something else (or nothing)
End If
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top