Solved Opening a recordset and filtering it (1 Viewer)

[KitaYama]

I need to open a recordset, filter it and work on the filtered version.

Method 1 : Unsuccessful (Before and after setting the filter result is the same)

    Dim XL As String
    Dim rs1 As DAO.Recordset
    Dim sql As String

    XL = CurrentProject.Path & "\book1.xlsx"
    If XL = "" Then Exit Sub
  
    sql = " SELECT [Field1]  AS OrderNo, "
    sql = sql & "[Field2] AS DrawingNo, "
    sql = sql & "[Field3] AS ManufacturingNumber "
    sql = sql & " FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XL & "].[sheet1$];"

    Set rs1 = CurrentDb.OpenRecordset(sql)
    rs1.MoveLast
    Debug.Print rs1.RecordCount                  ' Debug = 29'
    rs1.MoveFirst
    rs1.Filter = "[DrawingNo] LIKE '*C*'"
    rs1.MoveLast
    Debug.Print rs1.RecordCount                  ' Debug = 29'
    rs1.Close

Method 2 : Successful (before and after filtering result is different)

    Dim XL As String
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim sql As String

    XL = CurrentProject.Path & "\book1.xlsx"
  
    sql = " SELECT [Field1]  AS OrderNo, "
    sql = sql & "[Field2] AS DrawingNo, "
    sql = sql & "[Field3] AS ManufacturingNumber "
    sql = sql & " FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XL & "].[sheet1$];"

    Set rs1 = CurrentDb.OpenRecordset(sql)
    rs1.MoveLast
    Debug.Print rs1.RecordCount                  ' Debug = 29'
    rs1.Filter = "[DrawingNo] LIKE '*C*'"
  
    Set rs2 = rs1.OpenRecordset
    rs2.MoveLast
    Debug.Print rs2.RecordCount                  ' Debug = 13'
  
    rs1.Close
    rs2.Close

I'm trying to understand why filtering in first method doesn't return the expected record count.
Thanks for any insight.

 

[cheekybuddha]

Any reason you don't just filter before opening via the SQL?

 

[KitaYama]

Any reason you don't just filter before opening via the SQL?

Yes. There's a good reason.
I don't know how to.
Any advice?

thanks.

 

[The_Doc_Man]

It seems that you need to do it in the order you originally attempted - open RS1, filter it, open RS2.

Recordset.Filter property (DAO)

Office developer client VBA reference documentation

learn.microsoft.com

OR do like David suggests and add in your filter as a WHERE clause in the SQL before you open the recordset. Because really, that is pretty much all that would happen when you add a filter anyway.

OR (3rd option) Open RS1, filter it, .REQUERY it, see what it gives you.

 

[cheekybuddha]

Would this not work?

    Dim XL As String
    Dim rs1 As DAO.Recordset
    Dim sql As String

    XL = CurrentProject.Path & "\book1.xlsx"
    If XL = "" Then Exit Sub
 
    sql = " SELECT [Field1]  AS OrderNo, "
    sql = sql & "[Field2] AS DrawingNo, "
    sql = sql & "[Field3] AS ManufacturingNumber "
    sql = sql & "FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XL & "].[sheet1$] "
    sql = sql & "WHERE [DrawingNo] LIKE '*C*';"

    Set rs1 = CurrentDb.OpenRecordset(sql)
    rs1.MoveLast
    Debug.Print rs1.RecordCount
    rs1.Close

 

[KitaYama]

Would this not work?

    Dim XL As String
    Dim rs1 As DAO.Recordset
    Dim sql As String

    XL = CurrentProject.Path & "\book1.xlsx"
    If XL = "" Then Exit Sub

    sql = " SELECT [Field1]  AS OrderNo, "
    sql = sql & "[Field2] AS DrawingNo, "
    sql = sql & "[Field3] AS ManufacturingNumber "
    sql = sql & "FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XL & "].[sheet1$] "
    sql = sql & "WHERE [DrawingNo] LIKE '*C*';"

    Set rs1 = CurrentDb.OpenRecordset(sql)
    rs1.MoveLast
    Debug.Print rs1.RecordCount
    rs1.Close

I didn't try it in my tests because I thought the query is not yet built, so I can not use Where clause. I imagined I have to use Excel filter to filter the sheet, then open the recordset.

Tested your suggested version, as I had guessed I received error :
Run-time error 3061:
Too few parameters. Expeted 1.

Error line is : Set rs1 = CurrentDb.OpenRecordset(sql)

 

[KitaYama]

The most obvious difference is that you assign the filter value for the first case AFTER you open the recordset.

How can I filter a recordset before opening it? The source is not an Access table to be able to be filtered by WHERE clause. The source is Excel.

OR do like David suggests and add in your filter as a WHERE clause in the SQL before you open the recordset.

Tested. Received errors.

Thanks.

 

[cheekybuddha]

Sorry, answering from my phone so I did not notice the aliases.

What happens if you try:

    sql = " SELECT [Field1]  AS OrderNo, "
    sql = sql & "[Field2] AS DrawingNo, "
    sql = sql & "[Field3] AS ManufacturingNumber "
    sql = sql & "FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XL & "].[sheet1$] "
    sql = sql & "WHERE [Field2] LIKE '*C*';"

 

[KitaYama]

Works. Thanks.
Case is solved. But
By any chance, Do you know why method 1 above doesn't return expected result?

 

[cheekybuddha]

Works. Thanks.

By any chance, Do you know why method 1 above doesn't return expected result?

Not without testing, but I'm nowhere near a computer right now.

However, from the docs, it seems that your method 2 is how it should be done.

 

[KitaYama]

OR (3rd option) Open RS1, filter it, .REQUERY it, see what it gives you.

This one makes no difference. Still the same record count.
For now I corrected my code as @cheekybuddha and your suggestion and used WHERE before opening the recordset.

But I'm still eager to know why filtering a recordset doesn't change its record count.

Thanks.

 

[cheekybuddha]

I *think* an ADODB recordset behaves more like you would expect when filtered (but I only very quickly skim-read the docs)

 

[cheekybuddha]

From the ADODB link:

Remarks​

Use the Filter property to selectively screen out records in a Recordset object. The filtered Recordset becomes the current cursor. Other properties that return values based on the current cursor are affected, such as AbsolutePosition, AbsolutePage, RecordCount, and PageCount. This is because setting the Filter property to a specific value will move the current record to the first record that satisfies the new value.

 

[The_Doc_Man]

But I'm still eager to know why filtering a recordset doesn't change its record count.

I can give a guess. DAO recordsets (based on other discussions I've seen online) form an internal list representing the entire "result set" when you first open them. I'm guessing now (not swearing it to be true) but I don't think they let you change the result set once it is developed. But a second recordset CAN modify what it would return based on the current settings (including the FILTER) on the first recordset. Sort of like layered queries.

You want to know why? 'cause that's the way DAO got implemented. The better question is why it DOESN'T recompute. But MS doesn't make the code of its Office libraries OpenSource so we might have to remember to not hold our breath until they tell us the answer. Blue isn't a good facial color for me.

 

[CJ_London]

I don’t have a problem filtering but I alias the from part of of the sql as I have found the odd issue by not aliasing

try

sql = "SELECT * "
sql = sql & "FROM (SELECT [Field1]  AS OrderNo, "
sql = sql & "[Field2] AS DrawingNo, "
sql = sql & "[Field3] AS ManufacturingNumber "
sql =sql & "FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XL & "].[sheet1$]) AS X "
sql = sql & "WHERE [DrawingNo] LIKE '*C*';"

or

sql = "SELECT [Field1]  AS OrderNo, "
sql = sql & "[Field2] AS DrawingNo, "
sql = sql & "[Field3] AS ManufacturingNumber "
sql = sql & " FROM (SELECT *FROM [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XL & "].[sheet1$]) AS X "
sql = sql & "WHERE [Field2] LIKE '*C*';"

or

sql = "SELECT [Field1]  AS OrderNo, "
sql = sql & "[Field2] AS DrawingNo, "
sql = sql & "[Field3] AS ManufacturingNumber "
sql = sql & "FROM (SELECT * from [Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XL & "].[sheet1$] WHERE [Field2] Like '*'') AS X"