Is it possible to count the number of records in a filtered recordset?

Subject line says it all really.

Hi,

If you have the recordset in code then .RecordCount should do the trick.

TC

I've alreadytried that but it isn't allowed on a filtered recordset any more thoughts?

Hi,

Don't have that problem myself, but how are you creating the recordset and what type of recordset is it?

TC

is your code as follows? ( i used Control)

Adodc1.Recordset.Filter = "EmployeeID<=" & Val(Text1.Text)
MsgBox Adodc1.Recordset.RecordCount

if your code is similar then deff. you can get record count.

If not Post your Code

I'm using DAO in Access 97

not at work at the moment but it goes something like

rst.filter = "select * where etc etc"

JFK

Hi,

Using DAO in Access and you're correct. You cannot use .RecordCount for what you want. (You can in VB though!)

Instead you need to create a new recordset based on the filtered original one. Alternatively just create a new recordset with the filter as a WHERE criteria!

Here is some code to show what I mean:

Code:

---

    Dim db As Database
    Dim rst As Recordset
    Dim rstFilter As Recordset
   
    Set db = CurrentDb
    Set rst = db.OpenRecordset("TableName", dbOpenDynaset)

    ' Get the recordcount of the original recordset
    rst.MoveLast
    Debug.Print rst.RecordCount

    ' Set the filter on the original recordset
    rst.Filter = "Enter Filter in here"

    ' Create a new recordset based on the filtered original
    Set rstFilter = rst.OpenRecordset

    ' Get the recordcount of the new recordset
    rstFilter.MoveLast
    Debug.Print rstFilter.RecordCount

    ' Close it all down
    rst.Close
    rstFilter.Close
    Set rst = Nothing
    Set rstFilter = Nothing
    Set db = Nothing

---

Hope it helps.

TC