Agee Software, Inc.
Filter / Find Box for Access FormHere 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.
- 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