FIND IN ARRAY

[sal21]

I use this code to fill an array:

Code: Select all

Private Sub FILL_ARRY()

    If Not (RS Is Nothing) Then
        If (RS.State And adStateOpen) = adStateOpen Then RS.Close
        Set RS = Nothing
    End If

    Set RS = New ADODB.Recordset
    RS.CursorLocation = adUseClient

    SQL = "SELECT TAVOLI.IDTAVOLO, IIf([N] Is Null,0,[N]) FROM TAVOLI LEFT JOIN (SELECT TAVOLI.IDTAVOLO, Count(COMANDA_TAVOLI.IDTIPO) AS N FROM COMANDA_TAVOLI INNER JOIN TAVOLI ON COMANDA_TAVOLI.IDTAVOLO = TAVOLI.IDTAVOLO " & _
          "WHERE COMANDA_TAVOLI.DATA=#" & Format(DATA, "MM/DD/YYYY") & "# GROUP BY TAVOLI.IDTAVOLO) AS Q ON TAVOLI.IDTAVOLO = Q.IDTAVOLO"

    RS.Open Source:=SQL, _
            ActiveConnection:=CON, _
            CursorType:=adOpenForwardOnly, _
            LockType:=adLockReadOnly
    RS.Sort = ("IDTAVOLO")

    RS.MoveFirst
    Erase arMyData()
    arMyData = RS.GetRows()
    RS.Close
    Set RS = Nothing

End Sub
 

this return:

IDTAVOLO ConteggioDiTavoli
1 0
2 1
3 0
4 2
5 0
6 0
7 0
8 2
9 0
10 0
11 0
12 0
13 0
14 0
15 1
16 0
17 0
18 0
19 0
20 1
21 0
22 0
23 0

now based a var M (as integer) =8 i need to retrive the related value, 2:

Possible with a Join statement:

If InStr(1, Join((arMyData )), vbNullChar, M) >= 1 Then
Debug.Print result in find array
End If

or?
...

[HansV]

Why don't you search the recordset instead of the array?

[SpeakEasy]

You seem to do a lot of copying recordsets into arrays, and then later wanting to perform various database-like functions against that array. Why not just keep the data in a disconnected recordset?