Agee Software, Inc.
Filter / Find Box for Access Form
Here is how you can add a find box to a continuous form that lets you
search multiple text, numeric, date or boolean (Yes/No) fields. The sample form is called frmContact with a recordsource
called qryContact. For this example, the SQL for qryContact is:
SELECT * FROM tblContact
tblContact contains fields: ContactID, LastName, FirstName, Street, City, State,
Zip, Phone, StartDate, Sel. ContactID is numeric, StartDate is a date field. Sel
is a Yes/No field.
The vertical bar delimiters let you specify the beginning or end of a field.
- Create a textbox named txtFind at the top of frmContact. Set its label to Find.
- Modify qryContact to add this Where statement (It should all be one line):
SELECT * FROM tblContact WHERE '|' & ContactID & '|' & LastName
& ', ' & FirstName & '|' & Street & '|'
& City & ', ' & State & ' '
& Zip & '|' & Phone & '|' & Format(StartDate,'mm/dd/yyyy')
& IIF(Sel,'SEL') & '|' LIKE '*' & Forms!frmContact!txtFind & '*'
- Add a line of code to the AfterUpdate event of txtFind to requery the form:
Me.RecordSource = Me.RecordSource
- Add the following code to the GotFocus event of txtFind to clear the Find box and show all records.
On Error GoTo 0
If Me!txtFind > "" Then
Me!txtFind = Null
- To help the user remember how to use the Find, set the StatusBarText property
of txtFind to:
Find in: |ContactID|LastName, FirstName|Street|City|State|Zip|Phone|StartDate|SEL|
You can also set the ToolTip property of txtFind and lblFind to this value.
- If you Find for Smith, it would return records containing '123 Blacksmith Way'.
- If you find for |Smith|, it would return only records where the field matched Smith exactly.
- You can use comma for delimiters to find Smith, John or Dallas, TX.
- To find records where Sel is checked, find for |SEL|
- You can use the ? wildcard to match any single character. 12/??/2018
- Or use * to match any number of characters. 123 Park * Dallas
- If you have multiple numeric or date fields, use different delimiters like ~ or .I1234 for InvoiceID