As your database grows, locating specific records will involve more than a quick glance at a datasheet. In this article, learn five ways to locate specific records based on your needs.
What do you want to do?
Browse through all records
You can browse through records by using the TAB key when you want to move through one record at a time, in order, to locate a specific record. You can also browse through records in a table in Datasheet view using the record navigation buttons. The record navigation buttons are available at the bottom of the table or form.
1. Go to the first record
2. Go to the previous record
3. Current Record box
4. Go to the next record
5. Go to the last record
6. Open a new (blank) record
7. Filter indicator
8. Search box
Notes:
-
When you click in the Current Record box, you can type a record number, and then press ENTER to navigate to that record. The record number is counted sequentially from the start of the form or datasheet. It does not correspond to any field value.
-
To know if a filter has been applied, see the filter indicator button. If there is no filter applied or all the filters have been cleared, it displays No Filter. When it displays Filtered, you can click this button to remove the filter. Similarly, when it displays Unfiltered, you can click this button to apply the last filter that you used, if any.
-
When you enter text in the Search box, the first matching value is highlighted in real time as you enter each character. You can use this feature to quickly search for a record with a matching value.
Go to a specific record
You can go to a specific record in Access when you know which record you want to find. The Go to box lets you choose a particular record from a drop-down list and is usually added to forms.
-
To navigate to a specific record, click the arrow to the right of the Go to box, and then select a record from the drop-down list.
Note: If you know the first few characters of the record to which you want to navigate, you can type those characters in the Go to box to help you quickly find that record.
The Go to box displays only enough data to uniquely identify each record. When you select a record from the list, Access displays the rest of that record's data in the main area of the form.
Search for a specific record
You can search for a specific record in a table or form by using the Find tab in the Find and Replace dialog box. This is an effective choice for locating a specific record when the record that you want to locate satisfies specific criteria, such as search terms, and comparison operators, such as "equals" or "contains".
Note: You can only use the Find and Replace dialog box if the table or form currently displays data. This is true even if there are no visible records because a filter has been applied.
-
Open the table or form, and then click the field that you want to search.
-
On the Home tab, in the Find group, click Find, or press CTRL+F.
The Find and Replace dialog box appears, with the Find tab selected.
-
In the Find What box, type the value for which you want to search.
-
To change the field that you want to search or to search the entire underlying table, click the appropriate option in the Look In list.
Tip: The Match list represents your comparison operator (such as "equals" or "contains"). To broaden your search, in the Match list, click Any Part of Field.
-
In the Search list, select All, and then click Find Next.
-
When the item for which you are searching is highlighted, click Cancel in the Find and Replace dialog box to close the dialog box. Records that match your conditions are highlighted
For more information about how to use the Find and Replace dialog box, see the article Use the Find and Replace dialog box to change data.
Filter to see a limited number of records
You can filter to see a limited number of records when you want to see only the records that satisfy specific criteria and comparison operators. For example, to quickly narrow the records that are displayed, right-click a field whose value you want to match, and then select Equals, Does Not Equal, Contains, or Does Not Contain at the bottom of the shortcut menu. A filter can be turned on or off, which makes it easy to switch between filtered and unfiltered views of the same data. Unlike search, a filter only limits which of the records are displayed.
-
To apply a filter that is based on a selection, open a table or form.
-
To make sure that the table or form is not already filtered, on the Home tab, in the Sort & Filter group, click Advanced, and then click Clear All Filters, if that command is available.
-
Navigate to the record that contains the value that you want to use as part of the filter, and then click inside the column (in Datasheet view) or control (in Form, Report, or Layout view). To filter based on a partial selection, select just the characters that you want.
-
On the Home tab, in the Sort & Filter group, click Selection, and then click the filter you want to apply.
-
To filter other fields based on a selection, repeat steps 3 and 4.
For more information about how to apply a filter, see the article Apply a filter to view select records in an Access database.
Create a query to find a specific record
The techniques that you can use to search and filter records are very useful for finding specific records for the case at hand. However, you might want to perform the same search or filter operation regularly. Instead of reproducing a set of search and filter steps every time, you can create a query. A query is a powerful and flexible way to locate specific records because it lets you perform customized searches, apply customized filters, and sort records. You can build your own queries to help you focus on specific records and to answer specific questions. Once created, a query can be saved and reused, and can also be used in building forms and reports.
When you use a query to search or filter, you use criteria based on the type of data for which you are looking. Different kinds of information are stored as different types of data. For example, birthdays are stored as date/time data, whereas names are stored as text data.
Sometimes, you cannot find a particular record that you know exists. This can occur if you are viewing records in a form or a query that does not display certain records because of the value of a particular field. The following example demonstrates how to use a query to find records.
Suppose that you are using a database created by using the Access Issues template to track issues. The Issues table has a field that is named Status, which indicates whether a particular issue is active, resolved, or closed. You can create a query that displays issues whose status is closed by following these steps:
-
On the Create tab, in the Queries group, click Query Design.
-
Double-click Issues, and then click Close.
-
In the query designer, double-click the asterisk (*) in the Issues table. This helps make sure that the query will display all the fields from the records it returns.
Issues.* appears in the first column of the design grid, in the Field row. This indicates that all the fields from the Issues table should be returned.
-
In the query designer, double-click Status on the Issues table.
Status appears in the second column in the design grid, in the Field row.
-
In the second column of the design grid, clear the check box in the Show row. This helps make sure that the query does not display the Status field.
If you do not clear the Show check box in the Status column, the Status field will be displayed two times in the query results.
-
In the second column of the design grid, in the Criteria row, type ="Closed". This is your search criterion. This is how you make sure that the query will return only those records where the value of Status is "Closed."
Note: In this example, only one search criterion is used. You can use many search criteria for any given search by adding criteria to more fields, and by using the or row and the additional rows below or.
Your query is ready to run, and looks something like this:
-
On the Design tab, in the Results group, click Run.
Note: Unless you have already begun tracking issues and therefore have data in the Issues table — and you have set the status of at least one issue to "Closed" — the query will not return any results. However, you can save the query, and use it at any time in the future.
-
Press CTRL+S to save the query.
-
In the Save As dialog box, type a name for the query in the Query Name field, such as Closed Issues, and then click OK.
You now have a query that displays issues whose status is closed.
To learn more about how to create queries to help you find records, see the article Create a simple select query.
To learn more about search and filter criteria, see the article Examples of query criteria.
