Avatar of pdvsa
pdvsaFlag for United States of America asked on

Duplicates Query

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...
Microsoft Access

Avatar of undefined
Last Comment
pdvsa

8/22/2022 - Mon
Gozreh

What code are you using to open this query?
You can use a form and filter the form with the InvoiceNo.

Also, Why are you not setting the invoiceNo field as unique (Indexed - No Duplicates), so it will not allow duplicates?


SOLUTION
Daniel Pineault

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
pdvsa

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.  Its odd I know.  

Daniel:  kindly see error:
ASKER
pdvsa

Hi Dale,

You are right Dale. There could be >1 pmt per invoice.  Also, >1 credit too.  I dont have those cases but yes it will likely be the case at some pt. 
I however get a syntax error:
cursor goes to the ORDER line after I hit OK. 
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 WHERE InvoiceNo = [forms]![frmDupesInvNo].txtInvoiceNo GROUP BY InvoiceNo HAVING Count(*) > 1))
ORDER BY tblPayments.InvoiceNo;

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
pdvsa

Daniel, also not sure what Eval is and how its different than not having it in the code. 
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
pdvsa

Very nice.  Works great.   Thank you so much.