Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    London, Gtr London, England
    Posts
    75
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicates in array (Access 2000)

    Hi,
    Can anyone help? I have an array containing a variable number of codes:

    (21, 23, 4, 21)

    I want to take this array and deduplicate it, ie returning:

    (21, 23, 4)

    I am going round and round with this, with my only working solution so far being outputting the array contents to a temporary table, then reinserting them into the array. Can anyone please help point me in a better way?

    Thanks,
    J

  2. #2
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicates in array (Access 2000)

    Your temp table will probably be the fastest, as the SELECT DISTINCT operator will eliminate any duplicates and return the values in order.
    However, you could try either changing the array to a parameter array, saving it to a string variable, and using LEN and INSTR to locate and delete duplicates, or writing a couple of nested loops in code.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicates in array (Access 2000)

    Another way to handle it:

    Function RemoveDups(arr() As Variant) As Variant
    Dim col As New Collection
    Dim i As Integer
    Dim arrNew() As Variant
    On Error Resume Next
    For i = LBound(arr) To UBound(arr)
    col.Add arr(i), CStr(arr(i))
    Next i
    ReDim arrNew(1 To col.Count)
    For i = 1 To col.Count
    arrNew(i) = col(i)
    Next i
    RemoveDups = arrNew
    Set col = Nothing
    End Function

    Sub TestRemoveDups()
    Dim arrIn() As Variant
    Dim arrOut() As Variant
    Dim i As Integer
    ReDim arrIn(1 To 4)
    arrIn(1) = 21
    arrIn(2) = 23
    arrIn(3) = 4
    arrIn(4) = 21
    arrOut = RemoveDups(arrIn)
    For i = 1 To UBound(arrOut)
    Debug.Print i, arrOut(i)
    Next i
    End Sub

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Duplicates in array (Access 2000)

    What piece of your code Hans actually deletes the dups?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicates in array (Access 2000)

    The code doesn't actually delete anything. It adds the elements of the original array to a collection. Collections don't allow two elements to have the same key. Normally, trying to add a duplicate would cause an error, but this is silently suppressed by the line On Error Resume Next. So at the end, the collection object contains the unique entries from the array. These are then written to the output array.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Duplicates in array (Access 2000)

    Thanks for the explanation, i didn't know that collections don't allow dups.

    I learn something every day, thanks again Hans.

  7. #7
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Duplicates in array (Access 2000)

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> This is why I'm a daily Lounge browser.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •