Searching a database is crucial to make your data useful, so Access provides two ways to search a database:
- Search for a specific record.
- Use a filter to show one or more records that meet a specific criterion.
Searching MS Access for a specific record
The simplest type of search looks for a specific record. To search for a record, you need to know the data stored in at least one of its fields, such as a phone number or an email address.The more information you already know, the more likely Access will find the one record you want. If you search for all records that contain the first name Bill, Access could find dozens of records. If you just search for all records that contain the first name Bill, the last name Johnson, and a state address of Alaska, Access will likely find just the record you want.
To search for a specific record in an Access database table, follow these steps:
- In the All Access Objects pane on the left of the screen, double-click the name of the database table you want to search. Access displays the Datasheet view of your database.
- Click the Home tab.
- In the Find group, click the Find icon. The Find and Replace dialog box appears.
Search for a specific record in a database table. - Click in the Find What text box and type the data you know is stored in the record you want to find. For example, if you want to find the phone number of a person but you know only the person’s last name, type the last name in the Find What text box.
- Click the Look In list box and choose Current field or Current document (searches in all fields).
- (Optional) Click in the Match list box and choose one of the following:
- Any Part of Field: The Find What text can appear in any part of a field.
- Whole Field: The Find What text is the only text stored in a field.
- Start of Field: The Find What text can be only at the beginning of a field.
- (Optional) Click in the Search list box and choose one of the following:
- Up: Searches from the record where the cursor appears, up to the beginning of the database table
- Down: Searches from the record where the cursor appears, down to the end of the database table
- All: Searches the entire database table
- Click Find Next. Access highlights the field where it finds the text you typed in Step 4.
- Repeat Step 8 to search for more records that may contain the text you typed in Step 4.
- Click Cancel or the Close button.
Filtering an Access database
Searching a database is easy but somewhat limited because you can retrieve only a single record at a time that matches any text that you want to find. If you want to find multiple records, you can use a filter.A filter lets you tell Access to display only those records that meet certain criteria, such all records that contain people who earn more than $200,000 a year, are currently married, live in Las Vegas, Nevada, and own two or more cats.
To filter a database table, you must tell Access which field or fields to use as a filter, and then you must define the criteria for that filter. For example, if you want to filter your database table to see only records listing the names of people who are at least 65, you filter the Age field and set the criterion to Greater than or equal to 65.
Filtering simply hides all records in a database table that don’t match your criteria. Filtering doesn’t delete or erase any records.
Using an exact match for a filter in Access 2019
The simplest filtering criterion searches for an exact match. When you filter a field by an exact match, you’re telling Access, “I want to see only those records that contain this specific chunk of data in this particular field.” By using an exact match filter, you can display, for example, only the records that contain CA in the State field.To filter a database table, follow these steps:
- In the All Access Objects pane on the left of the screen, double-click the name of the database table you want to filter. Access displays the Datasheet view of your database.
- Click the Home tab.
- Click in the field (column) that you want to use as a filter.
- In the Sort & Filter group, click the Filter icon.A pop-up menu appears. You can either
- Select or clear check boxes from this menu.
- Continue with Steps 5 through 7 for more flexibility.
- Choose Text Filters. A submenu appears.
The Filter pop-up menu lets you specify the criteria for a specific field. - Choose a filter option, such as Equals, Begins With, or Contains. A Custom Filter dialog box appears.
The Custom Filter dialog box lets you further refine a filter criteria. - Type the data you want to find and click OK. Access displays your filtered data, and a filter icon appears in the column heading. Access remembers your filter settings. If you want to clear the filter, click the filter icon in the column heading; when a pop-up menu appears, choose Clear Filter.
You can clear a filter from a column heading.
To view all the data in your database table, click the Home tab. Then in the Sort & Filter group, click the Toggle Filter icon.
Filtering Access databases by form
One problem with defining filters in Datasheet view is that you have all your database table records cluttering the screen. To avoid this problem, Access lets you define filters by using a form, which basically displays an empty record so you can click the fields that you want to use to filter your database table.To define a filter by form, follow these steps:
- In the All Access Objects pane on the left of the screen, double-click the name of the database table that you want to filter. Access displays the Datasheet view of your database.
- Click the Home tab.
- In the Sort & Filter group, click the Advanced icon. A pull-down menu appears.
- Choose Filter by Form.
The Advanced pop-up menu lets you specify the criteria for a specific field.Access displays a blank record.
- Click in any field, then type the data you want to filter such as a last name.
- In the Sort & Filter group, click the Advanced icon and then click Apply Filter/Sort. Access displays a filtered view of your database table.
You can click the Toggle Filter icon again to view all the data in your database table.
Using a filter criteria in Access 2019
Searching for an exact match in a field can be handy, but sometimes you may want to see records that meet certain criteria, such as finding the names of everyone whose salary is greater than $50,000 a year. Instead of filtering by an exact match, you have to define the filter criteria.The type of data stored in each field determines the type of criteria you can create. Three common types of data stored in fields are text, numbers, and dates, which you can filter in different ways.
Common Criteria for Filtering Text Data
| Filtering Criteria | Description |
| Equals | Field must match filter text exactly. |
| Does Not Equal | Field must not match filter text. |
| Begins With | Field must start with the filter text. |
| Does Not Begin With | Field must not begin with the filter text. |
| Contains | Field must contain the filter text. |
| Does Not Contain | Field must not contain any part of the filter text. |
| Ends With | Field ends with the filter text. |
| Does Not End With | Field does not end with the filter text. |
Common Criteria for Filtering Numeric Data
| Filtering Criteria | Description |
| Equals | Field must equal filter number. |
| Does Not Equal | Field must not equal filter number. |
| Less Than or Equal To | Field must contain a number less than or equal to the filter number. |
| Greater Than or Equal To | Field must contain a number greater than or equal to the filter number. |
| Between | Field must contain a number that falls between two filter numbers. |
Common Criteria for Filtering Dates
| Filtering Criteria | Description |
| Equals | Field must equal the filter date. |
| Does Not Equal | Field must not equal the filter date. |
| On or Before | Field date must be equal or earlier than the filter date. |
| On or After | Field date must be equal or later than the filter date. |
- In the All Access Objects pane on the left of the screen, double-click the name of the database table you want to filter. Access displays the Datasheet view of your database.
- Click the Home tab.
- Click in the field (column) that you want to use as a filter.
- In the Sort & Filter group, click the Filter icon. A pop-up menu appears.
- Select the Filters option, such as Text Filters or Number Filters. A submenu of filter options appears.
The Filter pop-up menu lets you specify the criteria for a specific field. - Click a filter option, such as Between or Less Than. The Custom Filter dialog box appears. The Custom Filter dialog box contains the name of your filter option, such as Between Numbers or Less Than.
Type a value for your filter criteria. - Type one or more values in each text box in the Custom Filter dialog box, and then click OK. Access filters your database table according to your criteria.
- Repeat Steps 5 through 7 for each additional filter you want to add.
You can click the Toggle Filter icon again to view all the data in your database table.
Clearing a filter in Access 2019
When you apply a filter to a database table, you see only those records that match that filter. Access displays a Filtered message at the bottom of the screen to let you know when you’re looking at a filtered database table.To remove a filter so you can see all the records, choose one of the following:
- Click the Toggle Filter icon in the Sort & Filter group.
- Click the Filtered or Unfiltered button on the status bar near the bottom of the screen.
When you choose the Save command (Ctrl+S) to save a database table, Access also saves your last filter. The next time you open that database table, you’ll be able to use the last filter you created. If you want to save multiple filters, you’ll have to save them as a query.
See these handy Access keyboard shortcuts for further information.