Example Filtering on a Form.

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
--------------------------------------------------------------------------------

CHAPTER 4 - FORM LAYOUT
--------------------------------------------------------------------------------

CHAPTER 5 - FORM MODULE

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

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 txtFindAccountCode control then (lastly) add the string "*')". Using txtFindAccountCode="ABC", this results in :
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 txtFindCreationDate control to return a version we can usefully pass to SQL. In the code it says 'Format(CDate(Me!txtFindCreationDate),"\#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 txtFindCreationDate control but passed through a couple of functions first (explained above) then (lastly) add the string ")". Using txtFindCreationDate="1 Feb 2007", this results in :
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 txtFindAccountType control then (lastly) add the string ")". Using txtFindAccountType="1", this results in :
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 txtFindCreationDate. 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.

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