Build a search criteria form

        79 votes: ****      133,141 views      12 comments
by Allen Browne, 30 June 2006    (Access 95+)

Microsoft Access Tips for Serious Users

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


Search form screenshot

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:

Date range entry Result
From date only All records from that date onwards
To date only All record up to and including that date
Both From and To dates Only records between the two dates (both inclusive)
Neither From nor To No filter applied on this field

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.


Home Index of tips Top

<< Back to tutorials index




Please Note: This is a cached tutorial (reproduced with permission).
To view the tutorial at its original location click here.



Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent

Have your say - comment on this article.

What did you think of 'Build a search criteria form'?


1.

Rob Davis says...

14 April 2008

 
I use a public variable Criteria$ which is emptied as the 'search' form opens. On clicking 'Go' the VBA code explicitly checks each usable object and builds a concatonated Criteria$ before closing the search form and returning to the parent. The parent then checks to see if Criteria$ is null, if not, opens the target object (another form, more usually a report) using Criteria$ as the search parameter. I can send examples if you like.

2.

James says...

05 July 2008

 
Have been using Access for five years on and off. The may reason for going away from it was the inability to have effective search.

Everythingaccess.com has written something that can't be found elsewhere, a proper search.

http://www.everythingaccess.com/tutorials.asp?ID=Build-a-search-criteria-form


Thank you

James

3.

Wayne Phillips says...

05 July 2008

 
@James: Thank you for your comments, although we can't take direct credit for it as the article is written by Access MVP, Allen Browne.

Glad you found the article useful.

Wayne Phillips
http://www.everythingaccess.com

4.

Adam Kaydar says...

03 September 2008

 
The usefulness of the search form - great as it is - would be further enhanced if it included an option to filter for records that have "Null" data, i.e. are blank, and those that have "Not Null" data, i.e. are not blank, for all the filtrable fields.

Also, it would be useful to have an option to filter for an exact match on a date field and not only for "After" and "Prior To". Could you please advise how to achieve this?

Thatnks,
Adam Kaydar

5.

Melissa Miller says...

11 September 2008

 
This is great tool. Thanks for sharing. Very easy to setup and use. One question, is there a way to copy the results or dump them into a separate table so they can be copied? I am using this search form to pull a list of emails based on set criteria. I then need to be able to easily copy the email addresses to dump into Outlook.

Thanks,
Melissa Miller

6.

Dan says...

08 February 2010

 
Oh THANK YOU.

I was assigned to make a data base in Access. It has a lot of power and I was doing okay, right up to this sort of thing. I used to use Mac/Filemaker, and it's dead easy there. It was a stumper in Access. I've been flailing for a WEEK praying that the boss wouldn't notice the lack of progress.

In just a few minutes, this helped me put a project back on progress. My paycheck thanks you.

7.

Alfredo says...

10 February 2010

 
Super tool! Thanks.
What if I have two fields to show (employee and user), and the names are from a same table (people).
After that I would like to update some data at the result Sub-form

8.

Shawn says...

26 April 2010

 
I've been wanting to make a search form like this for some time. Allen's explanation along with the code made this very easy to understand and modify. I'll be using this in several places.

9.

David McMahon says...

20 June 2010

 
Spent all day looking for an appropriate search form example. This one worked great for my purposes.

10.

Rohit says...

08 July 2010

 
Just what I was looking for.

11.

lorrina says...

18 July 2010

 
I am new to Access and this example is extremely well noted as it explains what is going on. Thanks for sharing it

12.

Eric Hon says...

22 July 2010

 
This is a great piece to learn how to utilize search criteria within Access. I do have an issue with the coding that maybe I could get some guidance on. On my "main table", there is a field called "Assigned To", which is linked to another table called "Staff". I had this done in order for users to add new staff without me having to do it. The "Staff" table has a primary key for each staff name.

So, is there a way to code VB so that the search combo box (Me.Staff) can read the primary key off of the main table?

I am getting an error message (previous operation canceled) and I believe the root cause is that the coding is confused by alpha characters being read against the primary key of the staff's name.

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code
Please note: It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.   All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.


Site tools