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