Performing Access Datasheet Searches

Michael CharlesNov 03, 2010 | edited Nov 03, 2010 - by @MichaelCharles

Access offers you two methods of finding data that matches certain criteria when you are working in datasheet view,: filtering and searching. When you use filtering, Access temporarily hides data that does not match the criteria that you specify. When you search for data, Access leaves all records visible and jumps to the record(s) returned by your search. If you perform a filtering operation before doing your search, then Access will only search within the filtered records.

One simple way of performing a search is to use the Search box located next to the navigation control in the bottom left of the datasheet. Simply enter the text that you want to search. The search begins as soon as you start typing and Access moves to the first record containing the text entered and highlights the matching text in whichever field contains it.

Access performs the search by starting at the first record and scanning the contents of each column before moving on to the next. As soon as a match is found, you can repeatedly press the Enter key on your keyboard to find subsequent matches. When the last match is encountered, pressing Enter produces no further response.

When searching for records, there is always the classic Microsoft Office Find command, located in the Find group of the Home Tab of Microsoft Access Ribbon; or just use the keyboard shortcut: Control-F. This displays the Find and Replace dialog box. When a match is found, you can just keep clicking on the Find Next button to move to the next matching occurrence within the datasheet.

One of the benefits of using the Find command is that you are able to customize the way in which Access performs the search using the five options in the Find and Replace dialog. The first field is “Find What” and this is simply the text you want to find. Next, we have “Look In” which allows you to specify whether Access should search the entire datasheet or only the currently highlighted column. Thirdly, we have “Match” where you can specify whether the “Whole Field” has to match the search entry or whether “Start of Field” or “Any Part of Field” would be acceptable. Fourthly, the “Search” drop-down menu allow you to set the direction of the search: “Up”, “Down” or “All” which will cause Access to loop past the end of the table to ensure that all records have been examined. And, finally, “Match Case” allows you to make the search case-sensitive or case-insensitive.

If you would like to learn more about Access training courses, visit Macresource Computer Training, a UK IT training company offering Access training courses in London and throughout the UK.

Michael Charles+ Follow
joinedJul 31, 2021