---

MS Access 2003: Create a form where you can perform up to 3 search criteria that can be either text and numeric searches

This MSAccess tutorial explains how to create a form where you can perform a search that is up to three criteria that can be either text or numeric searches in Access 2003 (with screenshots).

Question: In Microsoft Access 2003/XP/2000/97, I'd like to have a button on a form that when clicked will open a popup form. On the popup form, the user can enter up to 3 search criteria that perform either a text search or a mathematical search.

Once the search has been performed, the original form will then display only those records that match the search criteria. I'd also like to have another button on the original form that when clicked will display a report with the search results.

Answer: We've provided a download to demonstrate how you can set up search functionality.

Download version in Access 2000

First, we've set up the form which displays the Customer records. The user can click on the Search button to perform up to 3 search criteria that are either text or numeric.

When the Search button is pressed, the following form will appear:

The user can select up to 3 search criteria.

When the Search button is pressed, the following message box will appear:

Your form will then display the search results as follows:

Pay close attention to how the RecordSource is set on the form called frmCustomers. The RecordSource on this form is updated using VBA code and not in the Properties window.

We've also included a "Show all customers" button in case you wish to view all customer records, and a "Generate Report" button to display a report of the search results.

Frequently Asked Questions

---

Question: I've tried the example on this webpage and I've encountered a problem while searching. The problem seems to occur when searching for anything with a single quote in it. Is there a workaround?

Answer: Single quotes can sometimes cause problems when writing queries in VBA as this example does. To workaround the error that you are receiving, try stripping out the single quotes when performing the search.

To do this, in the cmdSearch_Click event, try replacing the following code:

GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

with the following:

GCriteria = "Replace(" & cboSearchField.Value & ", chr(39), '')" & "   LIKE '*" & Replace(txtSearchString, "'", "") & "*'"

This code strips out the single quotes for the purposes of searching, but still displays the search results with the single quotes intact.