recordset.filter not filtering and i dont know why

Hi all.

thanks for any help.

below you will find a function that converts an array to a records set. seeing as the array size can change you will notice i use loops to define the field names. you will therefore have to trust me that their is a fieldname named period with a value of 1. the dataformat is set to varchar.

i've tried every combination of the .filter method that i can think of. can someone please take a stab at editing my code such that the filter works.

thanks

Code:

---

Private Function ConvertToRecordset(arrValues As Variant) As Recordset

    Dim oRs  As New Recordset
    Dim lRecCount
    Dim i As Integer
    Dim FieldName As String
   
    lRecCount = UBound(arrValues)
    Num_col = UBound(arrValues, 2)
   
   
    For icol = 0 To Num_col
    FieldName = arrValues(0, icol)
     
    oRs.Fields.Append FieldName, adVarChar, adFldMayBeNull
   
    Next icol


    oRs.Open
   
   
   
    For irow = 1 To lRecCount
   
    If (lRecCount > 0) Then
          oRs.AddNew


         
        For icol = 0 To Num_col
        FieldName = arrValues(0, icol)
        oRs(FieldName).Value = arrValues(irow, icol)
        Next icol
         


  End If
 
  Next irow
 
 



 
  Set ConvertToRecordset = oRs
 
  'up to here works


'"some_field NOT LIKE '%ABC%' "
ConvertToRecordset.Filter = "Period = '1'"
 
 
 
With ThisWorkbook.Sheets("Sheet1").QueryTables.Add( _
        Connection:=ConvertToRecordset, _
        Destination:=ThisWorkbook.Sheets("Sheet1").Range("A13"))
    .Name = "Contact List"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
End With
 
 
 
End Function

---

Your code seems a little convoluted.


I'm sure you can just copy an array to Excel.
Since this is a disconnected recordset I don't see what you'd gain from adding it as a connection (assuming it would allow you to) because you can't refresh it.


Anyway, you need to open a recordset from your recordset.

Code:

---

oRs.Filter = "Period = '1'"
Set ConvertToRecordset = oRs.openrecordset

---