Results 1 to 10 of 10

Thread: [RESOLVED] filter array and loop it

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Resolved [RESOLVED] filter array and loop it

    i fill the array wit:

    Code:
    .....
    
    Dim STRDBROWS() As String
    
    Set RS = New ADODB.Recordset
        SQL = "SELECT DISTINCT LICODART FROM " & NOME_DBF_LISTINI & " ORDER BY LICODART"
        'PER I FILE DBF NON CAMBIARE LE PROPRIETA' DEL CURSORE, DEVE SSERE SEMPRE: , adOpenKeyset, adLockPessimistic
        RS.Open SQL, CON, adOpenKeyset, adLockPessimistic
    
        NR = 0
        Erase STRDBROWS()
        RS.MoveFirst
        Do While Not RS.EOF
            ReDim Preserve STRDBROWS(NR) As String
            STRDBROWS(NR) = Trim(RS.Fields(0).Value)
            NR = NR + 1
            RS.MoveNext
        Loop
    ....
    how to filter the item into the array, base myvar="tress", and loop the result?

  2. #2
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: filter array and loop it

    Is there a reason for not making the "filter" part of the query, but adding a Where clause?
    Code:
    ' Having this inside you loop is very expensive!
      ReDim Preserve STRDBROWS(NR) As String
    
    ' Better have 
        ReDim STRDBROWS(RS.RecordCount - 1)
        Do While Not RS.EOF
            STRDBROWS(NR) = Trim(RS.Fields(0).Value)
            NR = NR + 1
            RS.MoveNext
        Loop
    '
    ' Resize the array to match the number of found records 
      If NR < RS.RecordCount And NR > 0 Then ReDim Preserve STRDBROWS(NR - 1)

  3. #3

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Re: filter array and loop it

    Quote Originally Posted by Arnoutdv View Post
    Is there a reason for not making the "filter" part of the query, but adding a Where clause?
    Code:
    ' Having this inside you loop is very expensive!
      ReDim Preserve STRDBROWS(NR) As String
    
    ' Better have 
        ReDim STRDBROWS(RS.RecordCount - 1)
        Do While Not RS.EOF
            STRDBROWS(NR) = Trim(RS.Fields(0).Value)
            NR = NR + 1
            RS.MoveNext
        Loop
    '
    ' Resize the array to match the number of found records 
      If NR < RS.RecordCount And NR > 0 Then ReDim Preserve STRDBROWS(NR - 1)
    tks bro.

    but really i i need to fill the array with 4 elements

    this is the new query:

    SQL = "SELECT LICODART, LICODLIS, LIPREZZO, LISCONT1 FROM " & NOME_DBF_LISTINI & " ORDER BY LICODART"

    and now, i need to select all item of array based the filter on LICODART="tess", and loop the result

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: filter array and loop it

    Code:
    SELECT LICODART, LICODLIS, LIPREZZO, LISCONT1 FROM " & NOME_DBF_LISTINI & " WHERE LICODART='tess'
    No need to filter the array itself or checking while looping through the recordset
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: filter array and loop it

    wonder why you don't want to put the Filter in the Query?
    here a sample how to use the recordset Filter

    Code:
    Private Sub Command1_Click()
       Dim Rs As ADODB.Recordset
       Dim sSQL As String
       Dim sLand As String
       
       sLand = "D"
       
          sSQL = "Select * From Adressen"
          Set Rs = New ADODB.Recordset
          Rs.CursorLocation = adUseClient
          Rs.Open sSQL, adoConnection, adOpenKeyset, adLockReadOnly
          
          'set your Filter
          Rs.Filter = "AD_Land = '" & sLand & "'"
          
          MsgBox Rs.RecordCount
          'set Filter back
          Rs.Filter = adFilterNone
    
    End Sub
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Re: filter array and loop it

    Quote Originally Posted by ChrisE View Post
    wonder why you don't want to put the Filter in the Query?
    here a sample how to use the recordset Filter

    Code:
    Private Sub Command1_Click()
       Dim Rs As ADODB.Recordset
       Dim sSQL As String
       Dim sLand As String
       
       sLand = "D"
       
          sSQL = "Select * From Adressen"
          Set Rs = New ADODB.Recordset
          Rs.CursorLocation = adUseClient
          Rs.Open sSQL, adoConnection, adOpenKeyset, adLockReadOnly
          
          'set your Filter
          Rs.Filter = "AD_Land = '" & sLand & "'"
          
          MsgBox Rs.RecordCount
          'set Filter back
          Rs.Filter = adFilterNone
    
    End Sub
    tks bro.
    but i need to fill array with recorset, because i use it for a other code

  7. #7
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: filter array and loop it

    Add your loop to fill the array between the 2 .Filter statements

  8. #8

    Thread Starter
    PowerPoster
    Join Date
    Mar 2005
    Posts
    2,580

    Re: filter array and loop it

    Quote Originally Posted by Arnoutdv View Post
    Add your loop to fill the array between the 2 .Filter statements
    not understand, sorry.

  9. #9
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: filter array and loop it

    Quote Originally Posted by luca90 View Post
    not understand, sorry.
    like this
    Code:
     Dim Rs As ADODB.Recordset
       Dim fld As ADODB.Field
       Dim sSQL As String
       Dim sLand As String
    '....etc
    
    
    
     Rs.Filter = "AD_Land = '" & sLand & "'"
          
          MsgBox Rs.RecordCount
      
    Do Until Rs.EOF
          For Each fld In Rs.Fields
             Debug.Print fld.Value & ";";
          Next
          Debug.Print
          Rs.MoveNext
       Loop
    
       'set Filter back
          Rs.Filter = adFilterNone
    setup your control.. Listview or Flexgrid or ??? whatever Control you have
    I just Print to the Debug window to check the rs

    good luck
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  10. #10
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: filter array and loop it



    Mix of code provide by Chris and by me, untested air code ofcourse
    Code:
        'set your Filter
        Rs.Filter = "LICODART = 'tess'"
          
        ReDim STRDBROWS(RS.RecordCount - 1)
        Do While Not RS.EOF
            STRDBROWS(NR) = Trim(RS.Fields(0).Value)
            NR = NR + 1
            RS.MoveNext
        Loop
    
        'set Filter back
        Rs.Filter = adFilterNone

Posting Permissions

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



Click Here to Expand Forum to Full Width