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

[JFK1965]

Subject line says it all really.

[TC99]

Hi,

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

TC

[JFK1965]

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

[TC99]

Hi,

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

TC

[buddu]

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

[JFK1965]

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

[TC99]

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:

    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