"Find Duplicates" Query

[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.

 

[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

 

[FJquestioner]

Thanks Gary. It worked perfectly.

"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