Problem when using criteria and count function

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)
 
 
 

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

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:-)