recordset.filter not filtering and i dont know why

[samuelimtech]

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




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

[jonh]

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.



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