Remove Duplicates in Array

[State Troopers]

Hi there,
I have a slight problem and cannot find where the "cause" of this problem
is. I am trying to remove duplicate values from an array. I have been trying
to use the dictionary way, but i am running in to problems.

Heres the code:


Dim strArray() As String

'ASSIGN VALUES TO ARRAY
If stDiff60 = 0 Then
strArray(intCount) = strDir
intCount = intCount + 1
rsMyRS.MoveNext
Else
rsMyRS.MoveNext
End If



removeDuplicates strArray



Private Sub removeDuplicates(ByRef arrName() As String)

Dim i As Long
Dim tempArr() As String
Dim d As New Dictionary
Dim n As Long

' This is the line where i get a "subscript out of range error
ReDim tempArr(0 To UBound(arrName))


For i = 0 To UBound(arrName)
If Not d.Exists(arrName(i)) Then
d.Add arrName(i), arrName(i)
tempArr(n) = arrName(i): n = n + 1
End If
Next

ReDim Preserve tempArr(n)

arrName = tempArr

End Sub




Thanks.
-State

 

[State Troopers]

Hey.
I understand what you are saying. Maybe I should have explained my case a
little better.
I have a table, like this, for insrance:

ID Category Date CLOSED
1 Ice 4/28/2006
1 Rocks 4/22/2006
1 Snow 4/21/2006
2 Ice 3/22/2006 CLOSE
2 Rocks 3/29/2006 CLOSE
2 Snow 3/29/2006 CLOSE
2 CLOSED 4/1/2006 CLOSE
3 Rocks 2/28/2006
3 Ice 4/25/2006
3 Snow 3/29.2006

As you can see, ID number 2 is closed.
What I am doing is figuring out which ID's have not been closed. But since
there are more than 1 occurance(spe?) of a single ID I cannot just ask for
all ID's - because of duplicates - for instance, if i wanted all ID's in an
array, i would end up with three ID number 1 entries.

So what I need is a procedure that will get rid of the duplicates, leaving
me with: 1, 3 (in this case) and not (1,1,1,3,3,3)

Thanks.
-State

 

[John Nurick]

This sounds like a simple query:

SELECT DISTINCT ID
FROM MyTable WHERE CLOSED <> 'CLOSE'
ORDER BY ID;

 

[State Troopers]

I found my mistake.
The Redim statement was in the wrong place. *sigh*

Thanks for your input though, very much appreciated.

Cheers
-State

 

[Michel Walsh]

.... and if you want to see if an "id" has all its row marked as "close":


SELECT id,
COUNT(*) As numberOfRow,
COUNT(iif(closed='close', 1, 0)) As numberOfRowsMakedAsClosed
FROM myTable
GROUP BY id


and that is compact, fast, and you don't really care about redim() or other
vba-errors, and it is ALWAYS up to date, since directly connected to the
real data, not to a copy in memory, of one PC.


Vanderghast, Access MVP