Filter Records using Complex Criteria

So far all you have done is find records that are like some other record in the table. But often criteria are more complex than that. For instance, you might want to find invoices for more than $1000 that are overdue, or projects that you need to finish in the next month. The Advanced Filter/Sort feature is a good introduction to queries, where you can ask complex questions of your database. Advanced Filter/Sort enables you to ask those complex questions: that is, create complex criteria, but the data that you are querying must be in one table. If it's not, you need to use the regular query feature. Whether you are using a query or an Advanced Filter/Sort, you need to define a new datasheet based on the table, choose which fields you want to display, and specify criteria that must be passed in order for a record to be displayed on the datasheet. Here is the Advanced Filter/Sort window and it looks a lot like the Query window, so if you learn about its features you will be ahead of the game when we get to queries. The following diagram contains information with respect to table name and fields:

You have learned the three main methods of filtering data in a datasheet in this module. You may wonder what more you can do with filters. Once you have learned to filter a datasheet you can use that skill on any datasheet, even those created by a query. And perhaps an even more useful feature is that you can create a form or a report from a filtered datasheet, forms and reports can be formatted, so this is a great way to create output to show to other people. When you create a form or report from a filtered datasheet, the form or report inherits the filter. So when new data is added to the table, the form or report will reflect all the data in the table filtered with the filter you defined.

• How Datasheet view filters are useful
  Since the view you get after you apply a filter contains only records with the values that you selected, the rest of the data remains hidden until you clear the filter. 1) Columns in datasheets and 2) controls in forms and reports that are bound to expressions do not support filtering. There are several types of filters and some of them are easy to apply and remove. Access contains some common filters that are built into every view. The availability of filter commands depends on the type and values of the field. To view the records of people whose birthdays fall during a specific month, click the BirthDate column, on the Home tab in the Sort & Filter group, click Date Filters, and then select the required date period.

Another approach for finding a specific record is to use the options that Access provides for sorting and filtering records. You can sort the records in text fields in ascending or descending order, and in number fields from smallest to largest. To sort and filter records, use the related commands in the

Sort & Filter group. 

You can also right-click a column name in Datasheet view and then click Ascending or Descending, or click the arrow to the right of a column heading to display a menu with sorting and filtering options. When you filter records, only the records that match the filter's criteria are displayed. This lets you whittle down a long list of records to find just a few records among many.

Here’s how the filter would look in SQL after correcting the missing date:

SELECT * 
FROM Projects
WHERE DueDate > #10/31/1999# AND DueDate < #12/31/1999#
ORDER BY DueDate ASC;

✅ Equivalent VBA Logic:
Below is the VBA code that applies the same filter programmatically, for example, on a form called `ProjectsForm`.

Private Sub ApplyDueDateFilter()
    Dim strFilter As String

    ' Build filter string for DueDate between 11/1/1999 and 12/30/1999
    strFilter = "DueDate > #10/31/1999# AND DueDate < #12/31/1999#"
    
    ' Apply the filter to the form
    With Forms!ProjectsForm
        .Filter = strFilter
        .FilterOn = True
        .OrderBy = "DueDate"
        .OrderByOn = True
    End With
End Sub

🧠 Notes:

• Date format is mm/dd/yyyy in U.S. regional settings.
• Replace ProjectsForm with the actual name of your form if different.
• You can place the ApplyDueDateFilter procedure in the form’s code module and call it from a button click or Form_Load event.

Using the Query by Example (QBE) grid you can create more specific criteria than you were able to when you used the Filter by Form window. This Simulation shows you how to go about creating an Advanced Filter to find the records you need.

1. As with any filter, the first step is to display the table you want to work with. You want to find projects that are due before September 1st that are not already complete. That information is in the Projects table, which is shown here. To open the Advanced Filter/Sort window choose Records>>Filter>>Advanced Filter/Sort from the menu.
2. You are now in the Advanced Filter/Sort window. The top pane is a box representing the Projects table – each field in the table appears in this box. You may have to scroll to see the ones you need, we have done this for you here. You need to define two criteria: Projects due before September 1st, this requires a criterion using the Due Date field; and Projects that are not complete, this requires a criterion using the Completed field. Records need to meet both criteria in order to be displayed on the filtered datasheet. In order to put them in the QBE grid all you need to do is double-click each field name in the table pane (top pane). Double-click on Due date, and then on Completed.
3. You need to state the criteria in language Access can understand. Fortunately, Access almost speaks English, and it certainly knows math, so you shouldn not find criteria expressions too hard to understand. The first criterion is that the due date be before 9/1/99. Access understands that the value should be less than 9/1/99 – so the criteria is simply <9/1/99. And you need to put it in the row labeled Criteria in the same column as the Due Date field. Now click on the criteria row in the Completed column (to the right of your current field) to add your next criterion.
4. Notice that Access put # around the date in the criteria you just typed. # is just an indication to Access that a value is a date. The next criterion is that the project be incomplete. You may remember that the Completed field appeared as a checkbox on the table datasheet – this is simply a yes/no field. Specify that the value of Completed is “no” by typing No into the Criteria row of the Completed column. The next step is to choose a sort order for the filtered records. To display the drop-down list arrow for sorting using the Due Date field, click the Sort row in the Due Date column.
   Note: Yes/No fields are stored by Access as zeros and ones (zero is false and one is true). So you could also specify that Completed must be equal to zero.
5. To list projects by due date (with the project that is due first listed first) you need to specify an ascending order for the Due Date field.
   Select Ascending from the Sort row drop-down list.
6. You have now specified criteria and sort order, and you’re ready to view the filtered datasheet. To do so, click the Apply Filter button on the toolbar (it looks like a funnel). Note: Like the Filter by Form window the Advanced Filter/Sort window allows you to specify Or criteria. Instead of defining them on a separate tab, use the Or row of the QBE grid.
7. Here is the filtered datasheet. In fact, since there are only two projects that need to be completed before September 1st, so you didn’t really need to sort the datasheet--but now you know how to do it. As usual with filtered datasheets, to see all records, click the Remove Filter button on the toolbar.

If you create a useful filter that you might want to use again you should save it as a query by clicking the Save As Query button on the Advanced Filter/Sort window. As long as you have not defined a new filter (using any of the three methods you learned in this module), your filter will still be defined when you

select Records>>Filter>>Advanced Filter/Sort. 

Review key glossary terms from this module, and try your hand at filtering and sorting the data in the Consulting database.
Matching the task to the appropriate filtering or sorting action.

The correct matching pairs:

1. Find your one client in Casa Grande: Use the Find and Replace dialog box.
2. Find the earliest start date in the Projects table: Sort the table in ascending order
3. Find projects for Lizard Web, Inc.: Use Filter by Selection
4. Find projects for Network Consultants that are not complete: Use Filter by Form
5. Find projects due in 2000 that have a start date in 1999: Use Advanced Filter/Sort

Queries draw various data sources together and present the combined information in useful views. They enable you to synthesize the raw data in your Access tables into meaningful analysis.
Queries are an essential part of any database application and are the tools that enable you and your users to extract data from multiple tables, combine it in useful ways, and present it to the user as a datasheet, on a form, or as a printed report.
"Queries convert data to information." To a certain extent, this statement is true. The data contained within tables is not particularly useful because, for the most part, the data in tables appears in no particular order. Also, in a properly normalized database, important information is spread out among a number of different tables. Queries are what draw these various data sources together and present the combined information in such a way that users can actually work with the data.

The term *query* originates from the Latin word quaerere, meaning "to ask" or "to inquire."
Over time, the word has evolved to encompass meanings such as question, inquiry, challenge, or investigation.
In the context of Microsoft Access, a query is essentially a question you pose to your database. It allows you to retrieve, filter, and analyze data stored in one or more tables based on specific criteria. Access provides query design tools that help you construct these questions easily. Depending on your needs, a query can be:

• Simple – asking for data from a single table (e.g., "Show all employees in the Marketing department").
• Complex – involving multiple tables with relationships, calculated fields, criteria filters, and aggregate functions (e.g., "List the total sales by region for the last quarter").

Queries are a fundamental component of Access and are essential for transforming raw data into meaningful information.