Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / November 2006

Tip: Looking for answers? Try searching our database.

counting records

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Jean-Paul De Winter - 25 Nov 2006 16:59 GMT
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
Rick Brandt - 25 Nov 2006 17:09 GMT
> Hi...
> I want to count the amount of records matching my filtering...
[quoted text clipped - 8 lines]
>
> What am I doing wrong?

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)

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt   at   Hunter   dot   com

Jean-Paul De Winter - 25 Nov 2006 17:55 GMT
Perfect... thank you both
JP

Rick Brandt schreef:
>> Hi...
>> I want to count the amount of records matching my filtering...
[quoted text clipped - 18 lines]
> Set TB_volume = db.OpenRecordset(sql_volume)
> volume = TB_volume.Fields(0)
Allen Browne - 25 Nov 2006 17:16 GMT
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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi...
> I want to count the amount of records matching my filtering...
[quoted text clipped - 9 lines]
> What am I doing wrong?
> Thanks
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.