Parsing Duplicates Using VBA

[BikeToWork]

I am trying to take one semi-colon separated field (Custodian) which contains multiple names and may or may not contain duplicate names. I split the field based on semicolon and created new ones for each name in Custodian (Cust0, Cust1, Cust2, etc.) Now I need to parse out the duplicates and combine the non-dupe fields to make the NewCustodian field, but am having a hard time with it. I made code that will mark each duplicate entry in the table tblDupes, which contains the fields DOCID, 0,1,2,3,4,5,6,7,8,9,10. I use the following code to mark which fields are dupes. This works okay except that if Cust0 and Cust4 are duplicates, it will enter -1 into Fields 0 and 4 in the tblDupes table, when I actually want to keep the values from one of the fields to concatenate into my new dupe free NewCustodian field.

Function DeDupe()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim arMulti() As String
Dim strOrigCust As String
Dim intUbound As Integer
Dim i As Integer
 
Dim strCust0 As String
Dim strCust1 As String
Dim strCust2 As String
Dim strCust3 As String
Dim strCust4 As String
Dim strCust5 As String
Dim strCust6 As String
Dim strCust7 As String
Dim strCust8 As String
Dim strCust9 As String
Dim strCust10 As String
Dim strCust11 As String
Dim intFirstFind As Integer
Dim intDupe1 As Integer
Dim intDupe2 As Integer
Dim intDupe3 As Integer
Dim strControlID As String
 
 
 
 
Set DB = CurrentDb
strSQL = "Select * From qryDupe1_MultipleCust"
Set RS = DB.OpenRecordset(strSQL)
 
Dim strDupesSQL As String
Dim DupeRS As DAO.Recordset
 
 
 
 
 
 
 
RS.MoveFirst
Do Until RS.EOF
    strOrigCust = RS!Custodian
    strControlID = RS![DOCID]
    arMulti() = Split(strOrigCust, ";")
    intUbound = UBound(arMulti())
    strDupesSQL = "Select * from tblDupes Where ControlID = '" & strControlID & "'"
    Set DupeRS = DB.OpenRecordset(strDupesSQL)
 
    For i = 0 To intUbound
 
 
        intFirstFind = InStr(1, strOrigCust, Nz(Trim(RS("Cust" & CStr(i)))))
        If InStr(intFirstFind + 1, strOrigCust, Trim(RS("Cust" & CStr(i)))) Then
            'Set DupeRS = "Select * from tblDupes"
            DupeRS.Edit
            DupeRS(CStr(i)) = -1
            DupeRS.Update
           'intDupe2 = InStr(intFirstFind + 1, strOrigCust, RS("Cust" & CStr(i)))
 
'            RS.Edit
'            RS("Cust" & CStr(i)) = Null
'            RS.Update
'
        End If
    Next
RS.MoveNext
Loop
 
MsgBox "Fields parsed", vbCritical, "FILE'S DONE"
 
 
End Function
 

Any advice is welcome and I hope this is not a double post. I posted this before and nothing showed up on the blog.

[jimatqsi]

Here's one way I might that sort of thing.

Do Until RS.EOF
    strOrigCust = RS!Custodian
    strControlID = RS![DOCID]
    arMulti() = Split(strOrigCust, ";")
    intUbound = UBound(arMulti())
    strDupesSQL = "Select * from tblDupes Where ControlID = '" & strControlID & "'"
    Set DupeRS = DB.OpenRecordset(strDupesSQL)
 
    strOrigCust=""     ' init our string so we can rebuild it
    For i = 0 To intUbound
     strOrigCust=replace(strOrigCust & ";","") ' remove this one if it is already there
    strOrigCust = strOrigCust  & arMulti(i) & ";"    ' now add this one
    next
    rs.edit
    rs!Custodian=strOrigCust  ' replace with our updated string
    rs.update
 
''        intFirstFind = InStr(1, strOrigCust, Nz(Trim(RS("Cust" & CStr(i)))))
 ''       If InStr(intFirstFind + 1, strOrigCust, Trim(RS("Cust" & CStr(i)))) Then
 ''           'Set DupeRS = "Select * from tblDupes"
 ''           DupeRS.Edit
 ''           DupeRS(CStr(i)) = -1
 ''           DupeRS.Update
 ''          'intDupe2 = InStr(intFirstFind + 1, strOrigCust, RS("Cust" & CStr(i)))
 
'            RS.Edit
'            RS("Cust" & CStr(i)) = Null
'            RS.Update
'
''        End If
''    Next
RS.MoveNext
Loop
 

[jimatqsi]

Of course you could substitute this for lines 11 and 12

if instr(strOrigCust,arMulti(i) & ";")=0 then
    strOrigCust=strOrigCust & arMulti(i) & ";"
end if

[zmbd]

My vote would be to use the instr() as J does in post#3.
Using your upperbound on the array and a for..next loop to step thru the array and avoid the potential bloat from temp-tables and the overhead of a record-set.

Out of curiosity why store the names in a single field, normally, I would have thought that a table to store the names as individual records and then concatenate them as needed via query just to avoid such a mess would be preferable.

[NeoPa]

I'd look at digging up a sort routine from the web then working with the sorted results.

[BikeToWork]

Thanks for the advice. It worked a treat.

[BikeToWork]

ZMBD, the semicolon separated fields are not from Access but rather are Relativity data, a Lit Support application which uses SQL Server for a back end and it is known as a "Multi Choice" type field and stores data in these fields in semicolon delimited format.

[zmbd]

eeegads the multi-value field somewhere else other than in an Access database!

shudder and tremble in fear the world is coming to an end (@_@)