How to: Filter grid columns

Let users narrow down the records with conditions on individual columns, either typed into a filter row or picked from a value list, and set the same conditions from VBA.

Requires WKL Forms Object Library 1.23 or later.

ShowFilterRow shows a row below the column headers with one condition per column. The funnel icon in a column header opens the column's value list. ShowFilterBar shows the active conditions below the records, with a switch and Clear filter. From VBA, ColumnFilter and ColumnFilterValues set the conditions, ColumnFilterEnabled suspends them, and ClearColumnFilters removes them.

Private Sub Form_Load()
    With Me!Grid1.Object
        .ShowFilterRow = True
        .ColumnFilter("City") = "Bo*"               ' begins with
        .ColumnFilter("Turnover") = ">= 100"
        .ColumnFilterValues("Country") = Array("US", "CA")
        Debug.Print .RecordCount, .ColumnFilterText
    End With
End Sub
 
Private Sub Grid1_BeforeColumnFilter(ByVal Column As String, ByVal Text As String, Cancel As Integer)
    If Column = "Turnover" And Text = "" Then Cancel = True      ' keep this condition
End Sub

Filter row conditions: text alone selects values that begin with it (Bo); a pattern with * or ? (*son, B?ker); a number or date alone selects equal values (100); a comparison (>= 100, <> Boston); and leer (empty) or not empty. Numbers and dates are read in the format of the Windows regional settings, without thousands separators.

• The column filter applies in addition to Filter, which limits what's read from the record source.
• The footer and group summaries follow the column filter.
• A condition that doesn't fit the column's data type is shown in red while it's being typed and doesn't filter anything; from VBA, it raises wkeInvalidValue.
• BeforeColumnFilter and AfterColumnFilter report the user's changes in the filter row, the value list, and the filter bar, but not changes made from VBA.
• The search bar (Ctrl+F, SearchText) searches all visible columns and highlights the matches without filtering.
• In server mode, the conditions are part of every statement sent to the record source.

• API reference: ColumnFilter
• How to: Search and filter nodes (search bar and Filter of the tree and list)
• How to: Filter tree list columns (the same filter in WklTreeList)
• WklGrid overview