Problem when using criteria and count function

partitionfrance

Forums

Hello,
First, excuse my bad english, i'm french :-)
I discover idea script, i have a lot of request to do in a big table. (> 100 000 records).
So i want to use "criteria" with a recordset.
like : rs.criteria " XXXX > 20150101 "
 
I have a problem :
My equation is ok, and when i browse my recordset the filter is ok ( rs.next).
But i browse the result with a loop like " While i < rs.Count"
The problem is : rs.Count don't return the number of record which match the criteria, but the whole records... So my loop don't work
Exemple : 1000 record , just 10 match the criteria. rs.Count return 1000 and not  10
It's a bug ?  if not how can i know the number of record which match the criteria ?
 
Tks
 

partitionfrance Fri, 09/09/2016 - 12:36

I'm using IDEA V9.
My source code :
 Dim rs As RecordSet
Set rs = base.RecordSet (100 000 records )
rs.Criteria=" NBPEOPLE> 1000" ( only 10 records match)
rs.tofirst
i=1
do
rs.next
'......
i++
Loop until i>rs.Count (rs.Count return 100 000 and not 10, so an error occured at the record number 11)
 
 
 

Brian Element Mon, 09/12/2016 - 08:09

Hello and welcome to the site.

The rs.count will always return the total number of records from within the database. The Criteria is only applied as you loop through all the transactions. So to obtain the 10 items based on the criteria you would need to add a counter within your loop to count how many transactions actually meet your criteria.

I have modified the example from the language browser and added a variable count1 to count the number of transactions that meet the criteria.  It uses one of the sample database so you should be able to paste this into the editor and try it out.

Hope this helps.

Brian

Option Explicit

Sub Main
	Dim db As database
	Dim rs As RecordSet
	Dim limit As Long
	Dim count As Long
	Dim result As String
	Dim vbCrLf As String
	Dim count1 As Long 'used to track the number of transactions that meet the criteria
	vbCrLf = Chr(13) + Chr(10)
	
	Set db = Client.OpenDatabase("Sample-Customers.IMD")

	' Create a RecordSet.

	Set rs = db.RecordSet
	
	' Define criteria for the RecordSet.
	
	rs.Criteria = "CREDIT_LIM >= 120000"
	
	' Move to the first record.
	
	rs.ToFirst
	
	rs.Next
	
	Limit = rs.Count
	' Set up an error handler.
	
	On Error GoTo EndOfRecordset
	
	' Process all of the records.
	
	For Count = 1 To Limit
		count1 = count1 + 1
		rs.Next

	Next 
	
	EndOfRecordset:
	MsgBox "Total transaction in database: " & Limit
	MsgBox "Total transactions with criteria CREDIT_LIM >= 120000: " & count1


End Sub

 

partitionfrance Sat, 09/17/2016 - 03:54

Thks, this solution works fine. ( for my opinion it's not very clean to generate an error, but this solution is simple and it work).
Thanks Brian:-)