Visual Basic :: Script To Remove Duplicate Values From Sql Server Table

Duplicate Values In One Table
We have created a table that has a listing of MANY email addresses from where we get spam. We are trying to block these email addresses. As you are aware these people change their address by just a little and it gets through. We are trying to go through the list and determine the similarities. I created a query that will take a certain amount of characters on one side of the '.'. I want to compare this field with the original field that has the full address.

I have 4,951 items in the DB and everything I have tried will compare each item 4,951 times. Therefore my Access times out. Here are a couple of the queries that I have tried, neither of them work.

Code:
SELECT Table1.Field1, AllItems.ShortField1
FROM Table1, AllItems
WHERE (('*' &(AllItems.ShortField1) & '*' Not like ( [Table1].[Field1])));

SELECT Table1.Field1, AllItems.ShortField1
FROM Table1, AllItems
WHERE (('*' &(AllItems.ShortField1) & '*' like ( [Table1].[Field1])));
Table1 is the original table with full addresses. AllItems.ShortField1 is the shortened field.
Can anyone help?

Lock Table Using ADO To Avoid Duplicate Values
Below is a function in Access 2000 - to retrieve the "Batch Number" and increment it by 1 in the table for next batch number. I am using VB6 front end for user interface. Using ADO (not DAO). I am trying to lock the table tblNextBatchNum so I don't get duplicate batch numbers inserted in two records when inserting records. With what I am doing it seems I am unable to lock the table - the recordset does not open in "adOpenDynamic" mode. Anybody has a solution for this. (see code below). Thanks. Jasminder.

Public Function fgBatchNumGet() As Long

Dim adCon As ADODB.Connection

Dim rsBatchNumGet As ADODB.Recordset

Dim strSQL As String

Dim lngNextBatchNum As Long

On Error GoTo Err_fgBatchNumGet

Set adCon = New ADODB.Connection

With adCon

.CommandTimeout = 60

.Open CurrentProject.AccessConnection

End With

strSQL = "tblNextBatchNum"

adCon.BeginTrans

Set rsBatchNumGet = New ADODB.Recordset

With rsBatchNumGet

.Open strSQL, adCon, adOpenDynamic, adLockOptimistic, adCmdTable

lngNextBatchNum = .Fields("NextBatchNum").Value

‘ COMMENTED MsgBox "Batch Num fetched access: " & CStr(lngNextBatchNum)

.Fields("NextBatchNum").Value = .Fields("NextBatchNum").Value + 1

.Update

End With

adCon.CommitTrans

rsBatchNumGet.Close

Set rsBatchNumGet = Nothing

adCon.Close

Set adCon = Nothing

Exit_fgBatchNumGet:

On Error GoTo 0

Exit Function

Err_fgBatchNumGet:

MsgBox Err.Number & " (" & Err.Description & ")"

adCon.RollbackTrans

adCon.Close

Resume Exit_fgBatchNumGet

Resume

End Function

Update An SQL Server Table With Values From An MS ACCESS Table
Hi everyone in the forum. I would like to create a VB script which updates a table in an SQL Server with values from a table from my local MS ACCESS. It would be very helpful if anyone could answer how to do that.
Pleeeeeeeeeeeease !!!

PS. I 'm sorry if a thread like that already exists and i didn't see it.

Values From Array To SQL Server Table
Hello!
How can i store the values (Long or Integer) that i have in an array to a field(what type?) in a table in SQL Server?Can i use the ADODB.Stream to do that? if yes how can i perform that?
I need to do the opposite also: retrieve the values that i have stored in my table in the database to my project.Any ideas how to perform this?
Thank's!

Updating All Values In A Table Collumn Using Vbb 6.0 And Ms Acces/ Ms SQL Server 2k
Hi coolz,

am developing a database application which at one point the user on clicking the command button, a specific collumn known as age should be incremented by only 1.

This should add 1 to all the values in that collumn of the table known as AGE through a single click...

am using ADO in both the desktop and distributed applications.

please help.

Thanks

Duplicate Remove
hi
i have a duplicate remove but what i want to do is if i have to listbox with listbox 1 with 1234 and 123 in it and also listbox 2 with 1234 and 342 in it how can i get it so it will find the 1234 in listbox 2 and remove it?

Remove Duplicate Value...
does any one knows how to remove duplicate values from combobox...???

Need it urgent...???

cheers...

Remove Duplicate Lines
how would i go about removing duplicate lines from a text file. (a big ish text file around 10,000 lines)

The duplicate lines are always next to each other so i suppose that makes it a lot easier.

Example:
1
2
2
3
4
2

It would only be required to remove the 2 in italics the 2 later on is ok. Speed isn't really needed either as long as it doesn't take like an hour or something silly.

Remove Duplicate From A Grid
I have a MSFlexGrid with 4 columns. I would like to delete duplicates (4 same columns). Is there a fast way or do I have to do a loop, check each one and delete them?
Thanks!
LD

Remove Duplicate Lines
I have a list of item codes for a data entry and trying to make a simpel vb app to help remove duplicates like:
The entries are in a multiline textbox
123456789
123449112 <- Orignal
787871213
123449112 <- Duplicate

So it removes only duplicates and keeps at leaste one of each code.

Duplicate Remove Function
Heres My Dup Remove Function, It Can Only Handle About, 1000 itmes, without freezing, any ideas making it handle more?? id like to get it to 5000+

Code:

Private Sub cmdDup_Click()
Dim Search1 As Long
Dim Search2 As Long
Dim KillDupe As Long
KillDupe = 0
For Search1& = 0 To List1.ListCount - 1
For Search2& = Search1& + 1 To List1.ListCount - 1
KillDupe = KillDupe + 1
If List1.List(Search1&) = List1.List(Search2&) Then
List1.RemoveItem Search2&
Search2& = Search2& - 1
End If
Next Search2&
Next Search1&
End Sub

Remove Duplicate Records
Hi,
Can somebody help with SQL Server query?

I have a SQL Server table with only 2 fields (Field1 & Field2). There are some duplicate records in the table and I try to delete all duplicates.

1) Here is what I use in Query Analyzer:
    BEGIN TRANSACTION
    delete
    from TableIndicators
    where EXISTS
    (
        select Field1, Field2,count(Field1),count(Field2)
        from TableIndicators
        group by Field1, Field2
        having count(Field1) > 1 and count(Field2) > 1
    )
    ROLLBACK TRANSACTION
This statement would delete the whole table.

2) When I use only sub query
    select Field1, Field2,count(Field1),count(Field2)
    from TableIndicators
    group by Field1, Field2
    having count(Field1) > 1 and count(Field2) > 1
it selects only duplicate records that is what I need.

Are there are any ideas why the whole statement (#1) does not work?

Thanks,
Romma

Remove Duplicate Listbox Entries
Hi,

I have searched but cannot find anything that works

I am wanting to remove any duplicate text from the list box....

heres an example of my code (does not work)

Quote:

Private Sub Command8_Click()

Dim itext As String

For i = 0 To List1.ListCount - 1
itext = List1.List(i)
Do
If List1.List(i) = List1.List(i) Then
MsgBox "2x song", , "ERROR"
End If
Loop
Next

End Sub

All help much appreciated!

Matt

Remove Duplicate Entries From A Collection
I'm trying to delete any duplicate entries from a collection thats been passed to a function. The idea i have in mind is to first add all the items in the collection to an array, then compare the array against the collection for duplicate values, but i'm not sure how to do this. I have:

Function DeDupe (Col as Collection) as Integer

For x = 1 To Col.count
ReDim Preserve checkarray(1 To x)
checkarray(x) = Col.item(x)
Next

For x = LBound(checkarray) To UBound(checkarray)

For y = 1 To Col.count
If checkarray(x) = Col.item(y) Then
MsgBox "Value: " & Col.item(y) & " removed", vbDefaultButton1, ""
Col.Remove (y)
End If
Next

Next

End Function

but all this does is remove the first value in the collection then throw a subscript out of range error.

Any ideas?

Remove Duplicate (or Triplicate Or More) Records
This thread is sort of a continuance of thread:
http://www.xtremevbtalk.com/showthread.php?t=187160
How do I remove duplicate, triplicate records (some are 4 or more the same)
I want to delete the entire records that are repeated.
Note that I mean delete from the db all together( using update I think is the method)

RichTextBox - Remove Duplicate Lines
I tried to do this using some code for removing duplicates in a listgbox, but I couldnt get it to work.

Anyone here have some code offhand that takes care of this?

Thanks ahead of time.

Remove Non Duplicate Rows From An Excel Column
Hi guys, I hope somebody can help me, How through visual basic or a formula can I remove from a column all the non duplicate data in an excel file?

I have a range of data and what I need is to find the duplicate data cells to work with it removing the non duplicated lines.

Any idea?

Thanks so much

Listbox And Textbox. Remove Duplicate Entries
All,

I have a form that has a textbox and a listbox. These are filled dynamically from user input. I need to be able to look at each item the listbox and determin if it exits anywhere in the textbox (This textbox has multipul entries). And if it does, I need to remove it from the textbox. I do not want to clear the textbox, just remove the duplicated item.

Any Ideas how to do this?

Remove Duplicate Entries From Text File....
hi, say i have a textbox with numbers like this:

345
645
878
8978
585
64
3
56

how would i make a program which removes duplicate entries from the textbox? i've more or less got a similar bit of code working which works for a listbox, but i'm kinda stuck with how to do it on a textbox. can anyone help?

Remove Duplicate Strings From Array Improvement?
I wrote a function to replace duplicate strings in an array with "". Since I'm going to have to convert it over to VBScript for ASP use, can this be improved for faster performance?

VB Code:
Private Function ChopArray(ByRef Arr() As String) Dim i As Integer 'outer loop counter Dim j As Integer 'inner loop counter Dim s As String 'temp store for current value 'loop through the array For i = LBound(Arr) To UBound(Arr) Step 1 s = Arr(i) 'if its not an empty string, and we're not at the last item in the array, 'then loop through the rest of the array to remove the duplicated strings If (Len(s) > 0) And (i <> UBound(Arr)) Then For j = (i + 1) To UBound(Arr) Step 1 If Arr(j) = s Then Arr(j) = "" Next End If Next End Function

Using Listview.finditem To Remove Duplicate Files....
I was wondering how to use the listview.finditem before loading items into the list, then form an if statement on either not adding if it is allready there and adding if it isn't..... can anyone tell me how this is done? or if there is an easier way to prevent duplicate items plz let me know, thanks

Crystal Reports: How Do I Remove Duplicate Records?
Hello,

How can I remove duplicate records?

I have are report as such:

Emp No Name     Hire Date Status Company/Subsidiary

7791     Anna Jackson     07/01/2002 Active Access
7997     Irene Hagedorn     08/01/2002 Active Access
7798     Alice Smith     08/01/2002 Active Access
7798     Alice Smith     01/01/2003 Active iSmart

Record Number 3: Displays a hire date of employee to 'Access' Company. Record 4: shows employee was employed in iSmart, a subsidiary of the main company.

The requirement of the report needs to display only record 4 and discard record 3 in the list.

What procedures should I do?

Thank you in advance,

Cristina.

How Can I Remove Duplicate Lines In A Text File?
I have made a function that removes duplicate lines in a text file but it does not always work here it is:

option Explicit

public Function RemoveDuplicateLines(FileName as string) as Integer

Open FileName for input Access Read as #1
Open App.Path & "integrity.tmp" for Output as #2

Dim LineIn(1 to 2) as string
Dim Impurities as Integer
Dim FirstPass as Boolean
Dim Last as Integer

FirstPass = true

Do
If EOF(1) = true then GoTo endof
'If it's the first time input 2 lines and compare them
'otherwise compare the last line with the next line
If FirstPass = true then

Line input #1, LineIn(1)
Last = 1

If EOF(1) = true then print #2, LineIn(1): GoTo endof

Line input #1, LineIn(2)
Last = 2

If EOF(1) = true then print #2, LineIn(2): GoTo endof

'Compare the lines
If LineIn(1) = LineIn(2) then

print #2, LineIn(1)

'Increment the defects
Impurities = Impurities + 1

else

print #2, LineIn(1)
print #2, LineIn(2)

End If

else 'It's not the first pass

If Last = 1 then

Line input #1, LineIn(2)
If EOF(1) = true then print #2, LineIn(2): GoTo endof

'Compare Line(1) with Line(2)

If LineIn(2) = LineIn(1) then
Impurities = Impurities + 1
print #2, LineIn(1)
else

print #2, LineIn(1)
print #2, LineIn(2)

End If

Last = 2

ElseIf Last = 2 then

Line input #1, LineIn(1)
If EOF(1) = true then print #2, LineIn(1): GoTo endof
'Compare Line(2) with Line(1)

If LineIn(1) = LineIn(2) then
Impurities = Impurities + 1
print #2, LineIn(2)
else

print #2, LineIn(2)
print #2, LineIn(1)

End If

Last = 1

End If

End If

Loop

endof:

Close #1
Close #2

Kill FileName
FileCopy App.Path & "integrity.tmp", FileName
Kill App.Path & "integrity.tmp"

RemoveDuplicateLines = Impurities

End Function

The problem with it is that it does two lines at a time so if my file is like this:

a
b
c
d
d
e
f

Then my output is like this:

a
b
c
d
d
e
f

Because 'd' was not reconized as a duplicate line. However if the file looked like this:

a
b
c
c
d
e
f

Then it would work because 'c' is an odd number.

My code looks complex because I though that it would work like this:

a <-- check this line with next line
b <-- check this line with next line
b <-- check this line with next line
c <-- etc..
d
e
f

But my code does this:

a <---\___ Check these two lines
b <---/
b <---\___ Check these two lines
c <---/
d
e
f

Can someone help me out??? Please!!

Duplicate Values
Hi all,

I am currently designing a program which enables people to book equipment for certain dates. I currently have a database with the following fields within the table (Name, Equipment, Date From, Date To, Complete). What I am trying to do is to check that when someone clicks the submit button that there is not existing record which could overlap the equipment and time factors.

This is what I have so far;

Code:

adbookings.recordset.movefirst
while not adbookings.recordset.eof
if (adbookings.recordset!Equipment=cboEquip.text) then
msgbox "Duplicate Value",vbokonly,"Error"
exit sub
else
adbookings.recordset.move 1
endif
wend
This works fine with search just the equipment records, however, I also want to search the dates records and the completed to make sure that one dates do not overlap and two that if it is completed then none of them are an issue.

I have tried entering another if statement after the initial if statement for the equipment recordset, however, it doesn't seem to enjoy this too much and is ineffective.

Can anybody give me a clue as to what I could do to make this work? I am also a newbie at this so please, please make it understandable.

Cheers & Beers
rat:wall:

No Duplicate Values Please!!
Hey Guys! I was wondering if someone could suggest a way to solve my problem. I have a combo box on my form that I would like to fill with values from a table on SQL server. I can fill the combo box with all the values (that's the easy part!) but I am going to be using the combo box as search criteria, and therefore do not want duplicate values in the combo box. The values are customer's names and there are customers in the database that have more than one record. So can anyone suggest a way to add a value, but only after making sure that there aren't any other values equal to it?? I'm sure this is an easy problem, but I just can't seem to wrap my head around it this morning!

Thanks in advance!!
~Ashizzle~

Duplicate Values.......
Does anyone have a little code snippet to remove dupicate values from a list box? Mine has seamd to have gone AWOL

Thanks

Si.

Duplicate Values
How do I take care of duplicate values in a primary key field. I have
developed a small application to store telephone numbers. The phone
number field is set as the primary key. I have done this to prevent the
entry of a phone number more than once. Is there a way for the
application to reject a duplicate entry before the error message is
generated or before the update?
--
Chilangisha B Changwe
email@removed

--
http://www.fastmail.fm - Choose from over 50 domains or use your own

Still Can't Get It! (Duplicate Values Lookup)
I have attached my zip file for reference. I am not sure what I am doing wrong. I am trying to make a 'time-saver' for the people that enter data into this database. The intent is: to enter a last name in the LastName box and hit the button.

Then using DLookup it would look up the last name and display matches in the subform below the main form, for the user to choose if it is the same person.

I can't get the 'Enter Parameter Value' box to quit coming up! Also the subform won't populate.

Duplicate Values In Array
Hey guys,

Very simple q- whats the best / an easy way of retrieving the indexes of any duplicate values in an array?

Thanks!

ka-noodle

Check For Duplicate Values?
I've created a sheetA that writest data to a dummy database sheetB.
The data is stored in sheetB, columnA.
I need some code that checks the input value from SheetA is not a duplicate in data SheetB.

Thanks for any help possible.

INSERT INTO And Duplicate Values
Ok,

Here is my problem...

I've got two tables. I want to insert records from the first table to the second one. Now that is fine except that there is a possibility that by inserting these records will create duplicate values in a field that contains a unique index.

Here is the solution in MS Access.

Run query from code or from macro and SetWarnings False. It then will insert the non-duplicate values and leave out the duplicates.

Now we must do this from VB using ADO. What is the best way to, either:

A. Tell ado/jet to go ahead with the INSERT INTO statement and just skip records with a duplicate value.

B. Remove all duplicate values before using the INSERT INTO statement.

??????

Thanks

Lloyd Cotten
CrucialCode

Preventing Duplicate Values
any ideas on how to prevent duplicate values being drawn from the database and inserted into a list-box?

Preventing Duplicate Values With Ado
Im using an ADO recordset to import data from a .DAT file into a table in an ACCESS DB. Ive got 3 fields in the table combined as the primary key. These fields are 1) store ID# 2) check# 3) check amount.

There cant be any duplicate rows with the same values for these 3 fields.
The DAT file has a lot of lines with duplicate values that need to be corrected.

When I loop through the .DAT file, reading/processing each line, and import the data into ACCESS with ADO, I'll get an error if the data from the .DAT file violates the "no duplicate values allowed for the primary key" condition.

What I need to do is write an error handler that cancels the addition of the data from the current line in the dat file into the DB, resets the current row in the ADO recordset, and resumes looping through the file, trying to process the rest of the data into the recordset/DB, keep going until EOF = true, and, again, handling the error if it occurs again.

As an example:
line 253 in .DAT file starts as:

0104000023434.....

line 403 also starts as:

0104000023434.....

so its a duplicate line, and needs to be corrected. VB will error upon reaching line 403, trying to read the values in the current row in the ADO Recordset, and trying to update the current row in the DB.

Heres some psuedocode of what Ive tried to do so far:

Code:
Private Sub IMPORTCHKS(FILESTR As String)
Dim TMPSTR As String
Dim LINEINT As Long

On Error GoTo ERRHANDLER

Set ERRS = CN1.Errors
ERRS.Clear

Open FILESTR For Input As #100
Do Until EOF(100)
RS1.AddNew
RESUMELOOP:
Line Input #100, TMPSTR
LINEINT = LINEINT + 1

RS1.STOREID = LEFT(TMPSTR,2)
RS1.CHECKNUM = MID(TMPSTR,3,2)
RS1.CHECKAMOUNT = MID(TMPSTR,5,9)

'...READ A BUNCH OF OTHER VALUES FROM DAT LINE STRING
'INTO RS1 FIELDS

'........

'HERES WHERE THE ERROR WILL OCCUR IF THE SAME COMBINATION
'OF STOREID/CHECKNUM/CHECKAMOUNT ALREADY EXISTS IN DB

RS1.Update

'AFTER TRAPPING THE ERROR THE FIRST TIME, AND RESUMING THE LOOP AT 'RESUMELOOP'
'VB WILL HALT ON 'RS1.UPATE', 2ND ERROR WONT BE CAUGHT

Loop
Close #100

Exit Sub

ERRHANDLER:

For Each ERR In ERRS
ERRSTR = ERRSTR & "ERROR # " & ERR.Number & " DESCRIP. " & ERR.Description
Next

DEBUG.PRINT FILESTR
DEBUG.PRINT LINEINT
Debug.Print ERRSTR
Debug.Print TMPSTR

'NOW THAT I KNOW WHERE THE DUPLICATE VALUE IN THE DAT FILE IS
'I WANT TO KEEP PROCESSING DAT FILE LINES, BUT I DONT KNOW THE CORRECT
'WAY TO CANCEL THE ATTEMPTED UPDATE, RESUME THE LOOP, RE-FILLING
'THE CURRENT ROW IN RS1 WITH VALUES FROM THE NEXT LINE IN THE DAT FILE
'AND, AGAIN, HANDLING THE ERROR IF NEXT LINE IS ALSO A DUPLICATE LINE, ETC., ETC., ETC.

RS1.CancelUpdate
'RS1.Delete adAffectCurrent

ERRS.Clear
GoTo RESUMELOOP

END SUB

I tried variations of the above strategy but I kept erroring out after trapping the error once, reaching the "GoTo RESUMELOOP", and again reaching the "RS1.UPDATE" statement. The error handler wouldnt even respond to the second error. Im guessing Im just doing something wrong with the ADO Recordset, but I just dont know what, or how to properly go about fixing the problem and resuming input into the RS1 object.

Duplicate Values Would Be Created
Okay I am trying diligently to get this DB stuf, and I think I'm doing okay, but when unexpected errors come up, when I am doing just as the code says in "a book" I get really upset.

The following code is supposed to move to and display the selected record from a DataList control on the the MouseUp() event.

Code:
Private Sub dlstUserList_MouseUp(Button As Integer, _
Shift As Integer, _
x As Single, _
y As Single)

AcceleDexDB.Recordset.Bookmark = dlstUserList.SelectedItem
End Sub
The items load properly and are listed in the data list control as expected.
However, I get this error...http://comlinkonline.tripod.com/error.JPG ...when I select one of the items.
Each of the controls on the form are linked to the DB via ADO Data Control and should update on the MouseUp() event, "according to the book I am reading"
I've checked the DataSource and DataField for each control and each are linked properly.
I've cleared ALL the code on the form and left only the MouseUp() event sub to try to get this to work.

Duplicate Values In Combo Box ...
Dear Friends,

In the MS Access database table ("Customers") there are just only 2 Customers added. But when I try to fill it in the Combo1 list using the following code it shows duplicate values in the combo1 list. How can I prevent it adding the duplicate values (Customers) in the list? I am calling (Call Fill_Fields) it in a Form_Load event.

Code:
Public Sub Fill_Fields()

If rs.State = adStateOpen Then
rs.Close
End If

rs.Open "Select DISTINCT CustomerName from Customers Order By CustomerName", db, adOpenDynamic, adLockOptimistic

Do While Not rs.EOF
Combo1.AddItem rs.Fields("Cn")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

End Sub

Thanks in advance.

vblearner6

Array And Duplicate Values
Hi,

I have an excel sheet containing values in column A and Column B. See bellow for details.

ABC
_________________________________
aaaalbatros
bbbbirds
ccccanine
dddsomething
eeeeeeotherwise
dddasdblablas

i have a loop that goes through the first column and if the lengthe is > 4, the routines picksup on ly the firt three letters of column B values and add a 1. However, because all these values must be unique, i wrote a function that should check for duplicates.

I am trying to find duplicate values in the checkDup function. However, i am having problems with it. I load the values in the array and the pass it to the function. if it is the same then add 1 and check again until there are no duplicate. then that value is the next unique value.

There is a twist: the first three letters can repeat and that is fine as long as the added interger from the counter is different. But the counter should be reset to 1 when a new set of values are present.

I need help with this. I am stuck.

Code:
Option Explicit
Dim MyArray(60)
Dim MyArray2(30)
Dim myval, myval2 As String
Dim i, j, count As Integer

Private Sub cmdAcct_Click()
'Dim i, j, count As Integer
Dim myval, myval2 As String
Range("C1:F60").Clear
j = 1
count = 0
For i = 2 To 60
Range("A" & i).Select
If Len(Range("A" & i).Value) <= 4 Then
Range("D" & i).Value = Range("A" & i).Value
ElseIf Len(Range("A" & i).Value) > 4 Then
count = count + 1
myval2 = Left(Range("b" & i).Value, 3) & count

'******************** this is to check for duplicate

MyArray(j) = myval2 'assigns the value to the array
myval = checkDup(MyArray(j)) 'then checks throught the function if it is a dup
j = j + 1

'********************
Range("D" & i).Value = myval

ElseIf Range("A" & i).Value = "" Then
Range("D" & i).Value = "****"

End If
Next i
'Call checkDup
Range("A1").Select
End Sub

Function checkDup(ParamArray MyArray())
Dim i As Integer
Dim result As String

'********************************
For i = 1 To UBound(MyArray)
result = result & MyArray(i) & vbNewLine
Next i

MsgBox result
End Function

Sql Query On Duplicate Values..??
hi experts..

i run out into little problem..how can you detect the duplicate values on th record and then dropp them at the same time...

the scenerio is:
all records are being shown on a grid now once i click on a button...to check duplicate values

all the record will be again populated on the grid displaying record with out a duplicate value for example on the field id...
id:

1
1
2
2
2
3

it will then display
1
2
3

and the duplicate records will de dropp.

i tried using distinct but when im populating a thousand records it doesnt seem to get the job done...

help...pls....

tnx.....

Still Unresolved - Finding Duplicate Values
How can I say: For each instance of this #, Do etc...

Example: I have one # in one column and another (2) to compare in other columns.

--- A --- B -- C
16851 16971 475
16972 16971 475
16971 16971 475
16973 16971 475

I want to say: For each sub#(column B), that is the same, then compare PCP#(column C), If they are ALL the same, Then delete all rows of instances of same sub# EXCEPT Where Sub#(column B) = Csr#(column A)...

Doe this make sense? Thanks.

Change Duplicate Values In A VB6 Array
In a small VB6 program I have an array - MyArr(500) as string - filled with random strings in no specific order.

I want to make sure that all values only occur once.

If for instance "zzt" occurs 4 times I want to rename them (except for the first one):

"zzt" (Note: NOT "zzt-1", but unchanged!)
"zzt-2"
"zzt-3"
"zzt-4"

I also want the new values to have the same index in the array, so no sorting is allowed.

How do I do this (efficiently)? It sound pretty easy to me but I can't figure out how to do this without a ridiculous amount of For:s and If:s...

Duplicate Refs Differing Values?
I have been using this:

=IF(A1=A2,A1 & " " & B1 & " " & A1 & " " & B2,"" )

(Ref no in col A, Value in Col B, above formula in col C Row 2)

to pull values where two ref nos are the same.
Does anyone know how to ask it to spot account nos that are the same but to report on those where the values differ please?

123456 85
123456 85
32145 62
32145 53

So something that would report on ref no 32145 having two differing values?

Thanks you

Check For Duplicate Values Dynamically?
Hi, I'm working with Access and I want a function that will check a specific column in a specific table for duplicates. Is there one built into Visual Basic? If so, what is it and how do you use it?

I have made my own, but the only part that doesn't work is the part in bold below. I want to be able to make a call to it by using a Form, Table, Specific Column in the table, and a control on the Form as arguements.

The column name is the part in bold below and is the only thing that doesn't work. If I manually type the Column name into the below function, i.e. replace the bold part below with Me!LastName (where "LastName" is the column to be searched for in the specified table) then everything works fine. I just don't want to have to type it in everytime into the function itself. I want to be able to pass the desired search column as a function arguement.

An example of how I want the function call to work would be:
<pre>
'ArguementDiscription Type
'-------------------------------------------------------------------------------------
'Me-Form to be searched ('Me' for current) (form)
'"CommonNames"-Actual name of table to be searched (string)
'"LNAME"-Name of column in field to be searched (string)
'txtCommon-Name of control on form that holds the text to check (control)

If Duplicate(Me, "CommonNames", "LNAME", txtCommon) Then
MsgBox "Common Name"
Else
MsgBox "Not a very common name"
End If

</pre>

I have tried to comment everything as well as possible.
I attached the original function for anybody who wants to edit it and return it to me.
Any and all help is appreciated!

<pre>

Function Duplicate(SForm As Form, STable As String, SCol As String, SBox As Control) As Boolean

'FUNCTION OPERATION:
'Search any table field, in any table, in any form, for any duplicates. Return True if one is found, otherwise return False

'ARUGEMENTS for Duplicate Function:
'SForm As Form - Name of form to be searched (use 'Me' for current form)
'STable As String - Name of the Table to search (form's record source)
'SCol As String - Name of the column in the table to search for duplicate
'SBox As Control - Name of field on form that holds possible duplicate

If IsNull(SBox.Value) Then: Duplicate = False: Exit Function

Dim Name As String: Name = SBox.Value
Dim SBoxName As String: SBoxName = SBox.Name

Dim strOriginalRecordSource As String 'store the original RS of form
strOriginalRecordSource = SForm.RecordSource

Dim strOriginalControlSource As String 'store the original RS of control
strOriginalControlSource = SBox.ControlSource

SForm.RecordSource = STable 'change desired table
SBox.ControlSource = SCol 'change record sources

DoCmd.GoToControl SBoxName
DoCmd.FindRecord Name, acEntire

If Me!SCol = Name Then
Duplicate = True
Else
Duplicate = False
End If

SForm.RecordSource = strOriginalRecordSource
SBox.ControlSource = strOriginalControlSource

End Function
</pre>

Checking For Duplicate Values In Access?
The code below enters a new username and password into my database but my question is this - how do I check against duplicate values so that if a user tries to create a username that already exists, they will be presented with a pop-up to tell them so...?

Code:
Private Sub cmdCreate1_Click()

Dim goConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim strConn As String, strSQL As String

strConn = "Provider=etc etc"

goConn.ConnectionString = strConn
goConn.Open
' Define a query string
strSQL = "INSERT INTO Users ([name], [password], [datejoined]) VALUES ('"
strSQL = strSQL & txtUsername1.Text & "', '"
strSQL = strSQL & txtPassword1.Text & "', '"
strSQL = strSQL & txtDate.Text & "')"
' Set up the Command object
adoCmd.CommandText = strSQL
adoCmd.CommandType = adCmdText
adoCmd.ActiveConnection = goConn
adoCmd.Execute

txtUsername1.Text = ""
txtPassword1.Text = ""

' Tidy up
Set adoCmd = Nothing
goConn.Close
Set adoConn = Nothing

End Sub

How To Restrict List Box For Duplicate Values
how do i restrict a listbox that it should not store duplicate values in it.

list1.additem txtstream(i)

How To Prevent From Saving Duplicate Values...
Dear Friends,

I am using MSAccess database and tbl_NoDuplicates has 4 fields (Name, City, Telephone, Fax). Now when I enter the duplicate (Existing values) record and press SAVE button it accepts. I want to write code which should check in all the fields of the table and make sure that there is no dulicates values then only it should SAVE the record otherwise give messages that it cannot be saved. Using textboxes for the input.

Thanks in advance.

vblearner6

Eliminating Duplicate Values In A ComboBox
I am reading a recordset and populating a combobox with 1 data element. Problem is there are duplicate values. I wish to eliminate them. Anyone know how to do this?

Thanks

Blake

Fetch Last Record From Duplicate Values
Hi, i have a sql table where there is no primary key defined on the key field (123 below) as it contains duplicates. From the below sample data if i need to know which is the last updated record what should i do ? In Foxpro the below is achieved using the scan() function.. pls help me with the query in VB as i have been struggling since long to get this up working.

Num Delvd Date Time Delay reason Entry_date Time

123 2002-09-03 0600 CUSTOM HELD NULL 2002-09-03 1209
123 2002-09-03 1600 CUSTOM HELD NULL 2002-09-03 1640
123 2002-09-04 1700 CUSTOM HELD NULL 2002-09-04 1734
123 2002-09-08 0900 CUSTOM HELD NULL 2002-09-08 1014
123 2002-09-12 1600 CUSTOM HELD NULL 2002-09-12 1635
123 2002-10-08 1500 NULL SIGN 2002-10-09 1505

How Can I Avoid Duplicate Values In Database
Hi,

I want to insert a record into the database and i want only unique values in my database using ado.

every thing is going fine but i am unable to restrict the duplicate values.How should i write code to avoid duplicate values in my database.

where URL is the field and it should be unique.

My code is :

Private Sub AddRecord()
On Error GoTo ErrorHandler
Dim SavedFlg As Boolean

' Add a new record using the recordset object
' Could be done using the connection object
SavedFlg = True
mRs.Open "SELECT * FROM csecurity", mCn, adOpenKeyset, adLockOptimistic
With mRs
If SavedFlg = True Then
mCn.BeginTrans
.AddNew
.Fields("WINDOWTITLE").Value = TxtIETitle.Text
.Fields("URL").Value = TxtIEUrl.Text
.Fields("ADATE").Value = Now
mRs.Update
mCn.CommitTrans
End If
' After updating the recordset, we need to refresh it, and then move to the
' end to get the newest record. We can then retrieve the new record's id

.Requery
.MoveLast
.Close
End With

ErrorHandler:

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Sub

any one can help me..

Thanks for advance