Search & Filters

Search & Filters

Before you can build formal queries, the ICDL Using Databases syllabus expects you to master the two simplest ways of finding information that is already sitting inside a table or form: the Find (or Search) command, and filters. Both tools work on data you already have. Neither one changes the structure of a table, and neither one permanently deletes a single record — they only change what you can see at any given moment.

Using Find to Search a Field

The Find command (sometimes labelled Search, depending on the application) lets you locate a specific word, number, or date stored in a field without scrolling through every record by hand. In Microsoft Access, Find is available from the Home ribbon or with the keyboard shortcut Ctrl+F, and it opens a small dialog box where you type the value you are looking for.

Key things to know about Find:

• It searches one field at a time by default, though you can widen the search to the whole table.
• You can search for exact matches, or for a value that appears anywhere within the field, or that matches the start of the field.
• Find jumps to (and highlights) the first matching record; pressing Find Next moves to the next match.
• Find does not hide any records — it simply moves the cursor to a matching value so you can view or edit that record.

Applying a Filter

A filter is different from Find: instead of jumping to one match, a filter temporarily narrows the displayed records to only those that meet a chosen condition, while every other record stays safely stored in the table — it is just hidden from the current view. Filters can be applied to a table in Datasheet view or to a form.

Common ways to apply a filter include:

• Filter by Selection — right-click (or highlight) a value in a field, such as a city name, and choose Equals to instantly show only records that match that value.
• Filter by Form — open a blank form-style grid and type criteria into one or more fields before running the filter, which is useful when you want to filter on more than one field at once.
• Text/Number/Date filters — use the drop-down arrow on a column heading to tick specific values, or to apply conditions such as greater than, contains, or between.

Because a filter only changes what is displayed, applying a filter is fast and reversible — it is well suited to a quick, one-off look at a subset of records, rather than a saved, reusable object like a query (covered later in this chapter).

A useful variation is Filter Excluding Selection, which does the opposite of Filter by Selection: instead of keeping only the records that match a highlighted value, it hides those records and displays everything else — handy for quickly ruling out one recurring value, such as hiding every record where Country = "South Africa" to focus on international records only.

Filters can also combine more than one field at once (for example, City = "Durban" and Status = "Active" together), whereas the basic Find dialog searches only the single field you have selected, or the whole record if you widen its scope. This is one of the clearest exam distinctions between the two tools — Find answers "where is this one value?", while a filter answers "show me every record that matches this rule."

Removing a Filter

Once you no longer need the narrowed view, you remove the filter (often called Toggle Filter or Remove Filter/Sort) to instantly restore the full, unfiltered list of records. Removing a filter does not delete anything — it simply switches the display back to showing every record in the table or form. You can reapply the same filter later using the toggle button without having to rebuild the criteria from scratch, since Access remembers the last filter you defined for that object until you close it.

Find vs Filter: Choosing the Right Tool

Understanding this distinction matters for the exam: Find is about locating, filters are about narrowing what is shown, and neither one is a permanent or saved object in the way a query is. Both, however, are described in the syllabus as the module's basic retrieval operations, forming the foundation for the more powerful query techniques covered in the rest of this chapter.

Common Exam Mix-Ups to Avoid

• Confusing Find with a filter. Find always leaves every record visible and simply repositions the cursor; a filter physically changes which rows are on screen. If a question describes records being "hidden" rather than "located," it is describing a filter, not Find.
• Assuming a filter deletes data. Removing a filter always brings back the full record set instantly, because filtering never removes anything from the table — it only controls the current view.

Because both operations act only on the current view of a table or form, they are ideal for quick, everyday lookups. When a task calls for something more structured — reusable criteria, results drawn from multiple related tables, sorting, or calculations — that is the signal to move on to a query, which is exactly where the rest of this chapter goes next.