Filter Records using Complex Criteria

Filter Access Records using Complex Criteria

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.

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

Complex Access Criteria