-
Nov 3rd, 2022, 02:29 AM
#1
Thread Starter
PowerPoster
[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?
-
Nov 3rd, 2022, 02:43 AM
#2
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)
-
Nov 3rd, 2022, 02:56 AM
#3
Thread Starter
PowerPoster
Re: filter array and loop it
Originally Posted by Arnoutdv
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
-
Nov 3rd, 2022, 03:46 AM
#4
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
-
Nov 3rd, 2022, 04:30 AM
#5
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.
-
Nov 3rd, 2022, 05:04 AM
#6
Thread Starter
PowerPoster
Re: filter array and loop it
Originally Posted by ChrisE
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
-
Nov 3rd, 2022, 05:16 AM
#7
Re: filter array and loop it
Add your loop to fill the array between the 2 .Filter statements
-
Nov 3rd, 2022, 05:21 AM
#8
Thread Starter
PowerPoster
Re: filter array and loop it
Originally Posted by Arnoutdv
Add your loop to fill the array between the 2 .Filter statements
not understand, sorry.
-
Nov 3rd, 2022, 05:37 AM
#9
Re: filter array and loop it
Originally Posted by luca90
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.
-
Nov 3rd, 2022, 05:39 AM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|