UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Detect Duplicates Invoice Id In A Table, Access 2003    
 
   
foxtrojan
post Jul 3 2017, 05:06 AM
Post#1



Posts: 1,074
Joined: 8-May 06
From: Singapore


How to detect duplicated Invoice ID in a Table by using VBA?
Go to the top of the page
 
Scott McDaniel
post Jul 3 2017, 05:17 AM
Post#2


UtterAccess VIP
Posts: 584
Joined: 18-February 09
From: North Augusta, SC


You can use DCount:

Dim InvoiceCnt As Integer
InvoiceCnt = DCount("*", "YourInvoiceTable", "Invoice=" & Me.txtInvoice)

Obviously you'd have to change Table, Field, and Control names to match those in your project.


--------------------
Go to the top of the page
 
foxtrojan
post Jul 3 2017, 08:09 AM
Post#3



Posts: 1,074
Joined: 8-May 06
From: Singapore


Thank you so much for your codes. Will do as advised.
Go to the top of the page
 
foxtrojan
post Jul 3 2017, 08:32 AM
Post#4



Posts: 1,074
Joined: 8-May 06
From: Singapore


It works, showing the number of Duplicates, but is it possible to show what are the invoice IDs?
Go to the top of the page
 
projecttoday
post Jul 3 2017, 11:38 AM
Post#5


UtterAccess VIP
Posts: 8,474
Joined: 10-February 04
From: South Charleston, WV


Not with Dcount.

CODE
SELECT COUNT(INVOICEID) AS CNTID, INVOICEID FROM INVTABLE
GROUP BY INVOICEID
HAVING COUNT(INVOICEID) > 1

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
foxtrojan
post Jul 6 2017, 09:06 PM
Post#6



Posts: 1,074
Joined: 8-May 06
From: Singapore


Thank you so much, I am bad in coding, where do I place the codes.?
Go to the top of the page
 
projecttoday
post Jul 6 2017, 09:32 PM
Post#7


UtterAccess VIP
Posts: 8,474
Joined: 10-February 04
From: South Charleston, WV


It's a query. So you have to create a query and put the code in the query using "SQL View".

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
nvogel
post Jul 13 2017, 08:40 AM
Post#8



Posts: 796
Joined: 26-January 14
From: London, UK


To state the "obvious", I expect the most important thing is to add a unique constraint on the Invoice ID so that duplicates won't happen again in future.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2017 - 01:23 AM