<div>
What code are you using to open this query?<br>You can use a form and filter the form with the InvoiceNo.<br><br>Also, Why are you not setting the invoiceNo field as unique (Indexed - No Duplicates), so it will not allow duplicates?<br><br><br>
</div>


Project finance

pdvsa

<div>
Gozreh, I didnt think of adding an index for no duplicates but since there are duplicates I dont think I can do that now unless I delete the duplicates but I need to keep them. &nbsp;Its odd I know. &nbsp;<br><br>Daniel: &nbsp;kindly see error:<br><a href="https://filedb.experts-exchange.com/incoming/2020/05_w21/1459019/Capture.JPG" rel="ugc"><img src="https://filedb.experts-exchange.com/incoming/2020/05_w21/1459019/Capture.JPG" class="fr-fic fr-dib"></a>
</div>


Capture.JPG

<div>
Hi Dale,<br><br>You are right Dale. There could be &gt;1 pmt per invoice. &nbsp;Also, &gt;1 credit too. &nbsp;I dont have those cases but yes it will likely be the case at some pt.&nbsp;<br>I however get a syntax error:<br>cursor goes to the ORDER line after I hit OK.&nbsp;<br><pre><code class="code-1 nolinks" id="code-20-43090702-1"><span>SELECT tblPayments.InvoiceNo, tblPayments.Amount, tblPayments.PaymentDate, tblPayments.ExpectedPmtDate, tblPayments.NotPaid, tblPayments.DateSentHB</span>
<span>FROM tblPayments</span>
<span>WHERE (((tblPayments.InvoiceNo) In (SELECT InvoiceNo FROM tblPayments as Tmp WHERE InvoiceNo = [forms]![frmDupesInvNo].txtInvoiceNo GROUP BY InvoiceNo HAVING Count(*) &gt; 1))</span>
<span>ORDER BY tblPayments.InvoiceNo;</span>
</code></pre>
</div>


<div>
Daniel, also not sure what Eval is and how its different than not having it in the code.&nbsp;
</div>


<div>
Very nice.&nbsp; Works great.&nbsp; &nbsp;Thank you so much.&nbsp;
</div>


<div>
<div class="content wysiwyg-content">
Experts,<br />
<br />
I have an issue with duplicate invoice numbers.<br />
I have a form built on tblPayments and [InvoiceNo] is the field I enter the invoice number.<br />
<br />
I built a duplicates query as follows:<br />
SELECT tblPayments.InvoiceNo, tblPayments.Amount, tblPayments.PaymentDate, tblPayments.ExpectedPmtDat<wbr />e, tblPayments.NotPaid, tblPayments.DateSentHB<br />
FROM tblPayments<br />
WHERE (((tblPayments.InvoiceNo) In (SELECT [InvoiceNo] FROM [tblPayments] As Tmp GROUP BY [InvoiceNo] HAVING Count(*)&gt;1 )))<br />
ORDER BY tblPayments.InvoiceNo;<br />
<br />
When I tab out of [InvoiceNo] the AfterUpdate event fires and the query appears but it shows all duplicates and I dont want to see them all. &nbsp;I want filter on the the current record [tblPayments.InvoiceNo], which would be the record I am entering. &nbsp;<br />
<br />
<br />
Let me know if it is not clear. <br />
thank you...
</div>
</div>


Experts,

I have an issue with duplicate invoice numbers.
I have a form built on tblPayments and [InvoiceNo] is the field I enter the invoice number.

I built a duplicates query as follows:
SELECT tblPayments.InvoiceNo, tblPayments.Amount, tblPayments.PaymentDate, tblPayments.ExpectedPmtDate, tblPayments.NotPaid, tblPayments.DateSentHB
FROM tblPayments
WHERE (((tblPayments.InvoiceNo) In (SELECT [InvoiceNo] FROM [tblPayments] As Tmp GROUP BY [InvoiceNo] HAVING Count(*)>1 )))
ORDER BY tblPayments.InvoiceNo;

When I tab out of [InvoiceNo] the AfterUpdate event fires and the query appears but it shows all duplicates and I dont want to see them all.  I want filter on the the current record [tblPayments.InvoiceNo], which would be the record I am entering.  


Let me know if it is not clear. 
thank you...

Duplicates Query

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.