counting records

  • Thread starter Jean-Paul De Winter
  • Start date
J

Jean-Paul De Winter

Hi...
I want to count the amount of records matching my filtering...
I wrote:

sql_volume = "SELECT meetoefeningen.Meetgroep FROM meetoefeningen WHERE
meetoefeningen.Meetgroep=1;"
Set TB_volume = db.OpenRecordset(sql_volume)
volume = TB_volume.RecordCount

Voule always returns 1

What am I doing wrong?
Thanks
 
R

Rick Brandt

Jean-Paul De Winter said:
Hi...
I want to count the amount of records matching my filtering...
I wrote:

sql_volume = "SELECT meetoefeningen.Meetgroep FROM meetoefeningen WHERE
meetoefeningen.Meetgroep=1;"
Set TB_volume = db.OpenRecordset(sql_volume)
volume = TB_volume.RecordCount

Voule always returns 1

What am I doing wrong?
Click to expand...

The RecordCount in a Recordset is not accurate until you do a MoveLast.
However; instead of doing that it would be more efficient to use a SQL statement
that does the count...

sql_volume = "SELECT Count(*) " & _
"FROM meetoefeningen " & _
"WHERE Meetgroep=1"
Set TB_volume = db.OpenRecordset(sql_volume)
volume = TB_volume.Fields(0)
 
A

Allen Browne

The RecordCount is the number of records accessed so far.
When first loaded, it will be 0 (if there are no records) or 1 (if there are
any.)
To get the full count, MoveLast.

The Move methods error if there is no record to move to, so:
Set TB_volume = ...
If TB_Volume.RecordCount > 0 Then
TB_Volume.MoveLast
End If
Debug.Print TB_Volume.RecordCount

For more info, see:
Traps Working with Recordsets - 10 common mistakes
at:
http://allenbrowne.com/ser-29.html
 
J

Jean-Paul De Winter

Perfect... thank you both
JP

Rick Brandt schreef:
The RecordCount in a Recordset is not accurate until you do a MoveLast.
However; instead of doing that it would be more efficient to use a SQL statement
that does the count...

sql_volume = "SELECT Count(*) " & _
"FROM meetoefeningen " & _
"WHERE Meetgroep=1"
Set TB_volume = db.OpenRecordset(sql_volume)
volume = TB_volume.Fields(0)
Click to expand...
 
Top