Consulting

Results 1 to 2 of 2

Thread: recordset.filter not filtering and i dont know why

  1. #1

    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



    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 
    
    
    Formatting tags added by mark007
    Last edited by samuelimtech; 04-20-2017 at 04:05 AM.

  2. #2
    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 
    
    
    Formatting tags added by mark007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •