If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Find Duplicates" Query
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. |
#2
|
|||
|
|||
"Find Duplicates" Query
"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 |
#3
|
|||
|
|||
"Find Duplicates" Query
Thanks Gary. It worked perfectly.
"Gary Walter" wrote: "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 |
Thread Tools | |
Display Modes | |
|
|