Microsoft Access Tips for Serious Users

Provided by Allen Browne, June 2006, updated March 2007.

Search criteria

A flexible search form provides several boxes where a user can enter criteria. An efficient search form creates the criteria from only those boxes where the user enters a value.

Download the search database (23KB zipped).

It illustrates how to use:

• Exact matches,
• Partial matches (wildcards),
• A range of values,
• Delimiters for each field type,
• Any combination of criteria,
• A design that is easy to extend.

Users sometimes try to provide optional criteria like this:
    Like "*" & [Forms].[Form1].[Text0] & "*"
That approach is inefficient and unsuitable for non-Text fields. It is also flawed, as it fails to return all records when the text box is left blank (i.e. the Nulls are excluded.)

The concept

The sample database uses a continuous form bound to the table or query that provides the fields for the results:

• The Form Header section has unbound controls for the criteria.
• The Detail section shows the search results, one per row.
• The Form Footer section shows the filter string (for debugging.)

Click the Filter button to show the results. Click Remove Filter to clear all criteria, and show all records.

Text boxes in the Detail section have the Locked property set, so users do not change the data. (You cannot use the form's AllowEdits property, as this prevents you using the unbound criteria boxes as well.)

Only the records that meet all criteria are returned.

The search code

Read this section in conjunction with the sample code in the search form, or view the code in a separate window.

The Click event procedure for cmdFilter looks at each unbound control in turn. If not null, it adds to the string strWhere. Each one ends in " AND ", so the next one can be added as well. The trailing " AND " is removed before applying the string to the Filter of the form.

Different field types require different delimiters, so the code demonstrates searching each type of field.

City is a Text type field in tblClient, so we add the quotes around the search value. If those quotes look strange, see Quotation marks within quotes.

MainName is also a Text field, so again we add the quotes. This time we used the Like operator with the * wildcard, to show how to search for a value anywhere in a field. The Like operator gives a slower search: a leading wildcard, in particular, prevents JET from using an index.

LevelID is a Number type field. Do not add the quote marks around values matched to a Number or Currency field.

IsCorporate is a Yes/No type field. If True, the client is a company (corporate entity); if False, the client is a person (individual.) The MainName field has company names for corporate clients, and surnames for individuals. When searching a Yes/No field, you need to give the user three choices: a) just Yes; b) just No; c) Yes or No (all). An unbound check box can do that if you set its TripleState property to Yes, but the interface is confusing, so we use a combo where the choices are obvious. The combo's Value List uses -1 for corporate; 0 for person, and 1 for both. The code therefore tests if the value is -1 or 0, and adds to the Where string. We add nothing to the filter sting for other cases (1 or Null.)

EnteredOn is a Date/Time type field, so the code adds the # delimiter around the date value in the string, and formats it with the native JET format to avoid problems with international dates. We provide From and To boxes so the user can specify a date range. If the field contains a time component, your criteria must use "less than the next day" so the final date is included. Results:

After building strWhere from the non-blank yellow boxes, the code tests its length less the 5 characters of the trailing " AND ". It shows a dialog if no criteria are found. Otherwise it removes the last 5 characters, assigns the result to the Filter property of the form, and sets FilterOn to True.

The reset code

The Click event procedure for cmdReset loops through all the controls in the Form Header section. Any text boxes or combos are set to Null. Any check boxes are set to False (i.e. unchecked.) Any other controls (labels, command buttons, ...) are ignored.

Then the filter is removed by setting the form's FilterOn property to No.

Looping through the controls means you do not need to change this code if you add extra filter boxes to the form in future.

Trouble-shooting and extending

If you do not get the Where string right in your database, the attempt to set the form's Filter may fail. To help with debugging, remove the single quote from this line:
    'Debug.Print strWhere
Now when it fails, press Ctrl+G to open the Immediate Window, and see what is wrong with your string. It must look exactly like the WHERE clause of a query. You can mock up a query using any literal values for criteria, and then switch to SQL View (View menu when in query design) to see an example of the WHERE clause you need to create.

The square brackets around field names are optional, unless your field names contain strange characters (spaces, leading numbers, other symbols except underscore.) The parentheses around the phrases are also optional, unless you mix operators. If you do mix ANDs and ORs, be aware that you get different results from:
    (a AND b) OR c
    a AND (b OR c)
where a, b, and c each represent phrases such as "City = ""New York""".

To use the search results for a report, build the Where string exactly the same way, and then use it as the WhereCondition for OpenReport:
    DoCmd.OpenReport "Report1", acViewPreview, , strWhere

We assume you have set the Allow Zero Length property to No for all Text fields in your table. If not, you must adapt this code to check for zero-length strings as well as nulls, and remember to do that in all queries and code in your database. Alternatively, you could programmatically fix the database with the code in the Problem Properties article.

If you want the form to open with no results, add these two lines to the Open event procedure of the form:
    Me.Filter = "(False)"
    Me.FilterOn = True
and add those two lines to cmdReset_Click as well, in place of:
    Me.FilterOn = False
Ultimately, the Filter string is something that evaluates to True or False. This expression excludes everything, because it is False for all records.

If a form returns no records and no new records can be added, the Detail section goes completely blank, and you face these display problems. For this reason, we did not set the form's AllowAdditions property to No, but cancel the form's BeforeInsert event instead.

To include a multi-select list box in your criteria, adapt the code shown in this article: Use a multi-select list box to filter a report.

To filter on other tables that are not even in the form's RecordSource, use a subquery.

Using a query instead

The sample database also contains a query named qryAlternativeApproach. This query reads the values from the text boxes on the form, but does not use any code.

For many reasons, the query approach is not really recommended:

• You must declare your parameters, to avoid the problems described in this article: Calculated fields misinterpreted.
• If you do declare parameters, you are likely to run into the bug described in this article: Parameter of type Text is evaluated wrongly.
• The query will not read the value of a value in the control on your form until you tab out of the control (which updates its Value property.)
• Bracketing of the ANDs and ORs is crucial (as explained above.)
• It is less efficient to execute.
• Access messes up the query if you switch to Design view:
  • Line-endings are lost.
  • Spurious brackets are added.
  • The ANDs and Ors in the WHERE clause is completely changed.
  • With the way Access changes the WHERE clause, you may run out of Criteria design rows.

For the SQL buffs, the query is:

PARAMETERS [Forms]![frmClientSearch]![cboFilterIsCorporate] Short,
  [Forms]![frmClientSearch]![txtFilterLevel] Long,
  [Forms]![frmClientSearch]![txtStartDate] DateTime,
  [Forms]![frmClientSearch]![txtFilterEndDate] DateTime;
SELECT tblClient.*
FROM tblClient
WHERE IIf([Forms]![frmClientSearch]![cboFilterIsCorporate] = -1, (tblClient.IsCorporate), 
    IIf([Forms]![frmClientSearch]![cboFilterIsCorporate] = 0, (NOT tblClient.IsCorporate), True))
  AND (([Forms]![frmClientSearch]![txtFilterMainName] Is Null) 
    OR (tblClient.MainName Like "*" & [Forms]![frmClientSearch]![txtFilterMainName] & "*"))
  AND (([Forms]![frmClientSearch]![txtFilterCity] Is Null) 
    OR (tblClient.City = [Forms]![frmClientSearch]![txtFilterCity]))
  AND (([Forms]![frmClientSearch]![txtFilterLevel] Is Null) 
    OR (tblClient.LevelID = [Forms]![frmClientSearch]![txtFilterLevel]))
  AND (([Forms]![frmClientSearch]![txtStartDate] Is Null) 
    OR (tblClient.EnteredOn >= [Forms]![frmClientSearch]![txtStartDate]))
AND (([Forms]![frmClientSearch]![txtFilterEndDate] Is Null) 
    OR (tblClient.EnteredOn < DateAdd("d", 1, [Forms]![frmClientSearch]![txtFilterEndDate])));

Conclusion

The technique of dynamically building the WHERE string in code has many uses: forms, reports, executing action queries, OpenRecordset(), even reassigning the SQL property of a QueryDef. Any situation that requires flexible criteria is probably a candidate for this approach.