Filtering is the process of displaying records that meet a given condition. For example, you can sort a set of records in alphabetical order, by date, by quantity (which restaurant orders the most bread or has the highest sales), and so on. Sorting is the process of organizing a set of records in a particular order. For example, you can find all the customers who run restaurants. Finding is the process of locating one or more records in database tables, queries, and forms. About the differences between finding, sorting, and filteringįinally, remember that finding is not the same process as sorting or filtering. An input mask is a type of formatting, and when Access encounters one, it selects the check box.Īlso, remember that if you search a lookup field - a field that uses a query to retrieve data from another table - and the source field in that second table has an input mask applied, Access enables the Search Fields As Formatted check box. You might wonder what logic drives Access to select or clear the Search Fields As Formatted check box.
That behavior may be confusing at first, because Access does not provide any visual cues or other indicators to indicate when you should select or clear the check box. If you clear the check box, your search operation probably will not return any results. Proper use of the check box can help ensure that your search operation succeeds.Īs you proceed, follow this rule of thumb: When Access selects the Search Fields As Formatted check box, leave the check box alone. As you go through the steps in this topic, you will see that Access sometimes selects the check box automatically so at times, you must select or clear the check box yourself. The Find and Replace dialog box provides an option called the Search Fields As Formatted check box. Understanding the Search Fields as Formatted check box For example, you can format empty or null fields to display a word such as "Unknown." You can then search for those blank fields by using "Unknown" as your search string. You can search for data based on a format applied to that data. You must open tables and query results in Datasheet view, and you must open forms in Form (normal) view, just as you would when entering data. Steps later in this section explain the basics of creating select queries and searching the results. If you need to use the dialog box to search multiple tables, you can create a select query to gather the desired data, and then search the query results. If you open the dialog box from within a form, you search the table that underlies that form. The dialog box searches only one table at a time it doesn't search the entire database. If you search on "mar", the find operation returns all instances of "market", "March", and any other records that match the pattern you specified. For example, suppose you have a datasheet (either a table or a query result) that includes information about customers, suppliers, products, and order dates. The dialog box treats your search string as a pattern and returns all records that match the pattern. For example, you can search on a format applied to data, and you can choose to match part or all of the data in a field. The dialog box resembles the Find tools that you see in other programs, but it contains some features that make it more useful for searching relational databases. You use the Find and Replace dialog box when you want to find and optionally replace small amounts of data, and when you are not comfortable using a query to find or replace data. Learn about using the Find and Replace dialog boxįind and Replace dialog box control referenceįind quotation marks and null or blank values
If you need information about other ways to update data, such as using update and append queries, see the article Update the data in a database.
This topic explains how to use the Find and Replace dialog box to find and optionally replace data in an Access database.
Access for Microsoft 365 Access 2021 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007 More.