Example Filtering on a Form.

[NeoPa]

CHAPTER 1 - TABLE OF CONTENTS (Including attached database)
CHAPTER 2 - INTRODUCTION
CHAPTER 3 - TABLE LAYOUT
CHAPTER 4 - FORM LAYOUT
CHAPTER 5 - FORM MODULE
CHAPTER 6 - CODE DISCUSSION (FILTER CONTROLS)
CHAPTER 7 - CODE DISCUSSION (THE REST)

--------------------------------------------------------------------------------

CHAPTER 2 - INTRODUCTION

This is a basic example and works with some unbound selection items on an otherwise bound form with no complicated SubForms or anything.
In this example I have a Form called frmAccount bound to table tblAccount.
The form has all of the table fields shown as (bound) controls (Mainly TextBox - name preceded by 'txt', but also a ComboBox - cboAccountType) . It also has three extra controls to enable the operator to select items by AccountCode, CreationDate & AccountType. These fields have been chosen for illustrative purposes only and are not supposed to reflect any real-world situation accurately.
To avoid confusion, it is a good idea to select names that have meaning, reflecting their contents, while avoiding special characters (including spaces) and names which are already in use for something else. A good example of the latter is 'Date'. Using this as a field or control name is a problem in waiting.

--------------------------------------------------------------------------------

CHAPTER 3 - TABLE LAYOUT

Table Name=tblAccount
AccountID; Autonumber; PK
AccountCode; String
AccountName; String
CreationDate; Date/Time
AccountType; Numeric; 0==>Control (Internal Usage); 1==>Supplier; 2==>Customer
SalesThisYear; Numeric

--------------------------------------------------------------------------------

CHAPTER 4 - FORM LAYOUT

Form Name=frmAccount
txtFindCode; Unbound
txtFindCreationDate; Unbound
cboFindAccountType; Unbound
txtAccountID; Bound to tblAccount.AccountID
txtAccountCode; Bound to tblAccount.AccountCode
txtAccountName; Bound to tblAccount.AccountName
txtCreationDate; Bound to tblAccount.Creation
cboAccountType; Bound to tblAccount.AccountType
txtSalesThisYear; Bound to tblAccount.SalesThisYear

--------------------------------------------------------------------------------

CHAPTER 5 - FORM MODULE

Option Compare Database
Option Explicit
 
Private Sub txtFindAccountCode_AfterUpdate()
    Call CheckFilter
End Sub
 
Private Sub txtFindCreationDate_AfterUpdate()
    Me!txtFindCreationDate = IIf(IsDate(Me!txtFindCreationDate), _
                                 Format(Me!txtFindCreationDate, "d mmm yyyy"), _
                                 "")
    Call CheckFilter
End Sub
 
Private Sub cboFindAccountType_AfterUpdate()
    Call CheckFilter
End Sub
 
'CheckFilter produces the new Filter depending on the values currently in
'txtFindAccountCode, txtFindCreationDate & cboFindAccountType.
Private Sub CheckFilter()
    Dim strFilter As String, strOldFilter As String
 
    strOldFilter = Me.Filter
    'txtFindAccountCode - Text
    If Me!txtFindAccountCode > "" Then _
        strFilter = strFilter & _
                    " AND ([AccountCode] Like '" & _
                    Me!txtFindAccountCode & "*')"
    'txtFindCreationDate - Date
    If Me!txtFindCreationDate > "" Then _
        strFilter = strFilter & _
                    " AND ([CreationDate]=" & _
                    Format(CDate(Me!txtFindCreationDate), _
                           "\#m/d/yyyy\#") & ")"
    'cboFindAccountType - Numeric
    If Me!cboFindAccountType > "" Then _
        strFilter = strFilter & _
                    " AND ([AccountType]=" & _
                    Me!cboFindAccountType & ")"
    'Debug.Print ".Filter = '" & strOldFilter & "' - ";
    'Debug.Print "strFilter = '" & strFilter & " '"
    'Tidy up results and apply IF NECESSARY
    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
End Sub

--------------------------------------------------------------------------------

CHAPTER 6 - CODE DISCUSSION (FILTER CONTROLS)

Each of the controls' filter part code starts with it checking that it is not empty. Any value (Text, Date or Number) will return true when checked as greater than "".
Each starts with " AND " but this is stripped from the first one found later before being applied.
Each check built is surrounded by parentheses () to ensure the logic is not confused with other checks.

TEXT
The delimiter for text literals is (').
The Text check is done as a 'Like' construct as the Account Code would be likely to be unique so = would not mix well with the others as a composite check. We start with the string " AND ([AccountCode] Like '" then add (string append) the value from our txtFindAccountC ode control then (lastly) add the string "*')". Using txtFindAccountC ode="ABC", this results in :

 AND ([AccountCode] Like 'CUS*')

This will find any record where the [AccountCode] starts with "CUS".

DATE
The delimiter for date literals is (#).
Firstly, to describe the manipulation of the txtFindCreation Date control to return a version we can usefully pass to SQL. In the code it says 'Format(CDate(M e!txtFindCreati onDate),"\#m/d/yyyy\#") & ")"'. As we've used a TextBox to enter a date into, we have little control of what the operator enters. They may enter "1 February 2007" or they may enter "1/2/2007" in England or "2/1/2007" in the USA. Whatever is entered, CDate() will convert, using the local settings, into a valid Date/Time value. SQL requires that any date literal be unambiguous but insists that the USA format of short date be used (m/d/y) rather than the local one whichever that may be. Read (Literal DateTimes and Their Delimiters (#).) for a more detailed explanation.
So, we start with the string " AND ([CreationDate]=" then add (string append) the value from our txtFindCreation Date control but passed through a couple of functions first (explained above) then (lastly) add the string ")". Using txtFindCreation Date="1 Feb 2007", this results in :

 AND ([CreationDate]=#2/1/2007#)

This will find any record where the [CreationDate] is 1 February 2007.

NUMBER
There is no delimiter required for numeric literals.
We start with the string " AND ([AccountType]=" then add (string append) the value from our txtFindAccountT ype control then (lastly) add the string ")". Using txtFindAccountT ype="1", this results in :

 AND ([AccountType]=1)

This will find any record where the [AccountType] is 1 (Customer).

--------------------------------------------------------------------------------

CHAPTER 7 - CODE DISCUSSION (THE REST)

AFTERUPDATE EVENT PROCEDURES
The first three procedures all call the same subroutine (CheckFilter) as the whole filter needs to be rebuilt whenever any of its constituent parts is changed.
To show consistently (not necessary for the logic) we check the data entered in txtFindCreation Date. If it is a valid date then we format it as "d mmm yyyy". Otherwise it is reset.
We'll go through each control's part of the filter in detail but firstly I've noticed that many people have problems distinguishing what is processed by VBA from what is passed to and processed by the SQL engine (interpreter). It may help you to understand if you uncomment the 'Debug.Print lines and view the results after selecting a value or some values from the 'Find' controls. This comes between the VBA processing and the SQL so it may help to see it at that stage.

APPLYING THE FILTER
Firstly, after stripping the first " AND " from the start, we check that the filter is actually different from the current one.
If it is, we need to set the filter and we also determine if filters should be used (.FilterOn) depending on whether or not strFilter is blank.

--------------------------------------------------------------------------------

[NeoPa]

A related article can be found at Cascading Combo/List Boxes.

[NeoPa]

A fuller version of this which includes cascading filter controls can be found at Cascaded Form Filtering.