Sorting/Filtering Numbers and Dates

As mentioned already, you can also sort the records in reverse incremental order. An example would be 5, 4, 3, 2, 1. To arrange a list of records in reverse incremental order, click the column header or a field under the column. Then, in the Sort & Filter section of the Ribbon, click the Descending button . As an alternative, you can right-click the column header or a field under it, and click Sort Largest to Smallest. As always, when you have finished sorting, you should dismiss the sorting. You can also sort numeric fields on a form in Form View. Remember that the difference with a Datasheet View is that the form in Form View would display one record at a time. To sort the records of a form in Form View or Tabular View in incremental order, on the form, click the control or its label. Then, in the Sort & Filter section of the Ribbon, click the Ascending button . You can also right-click the control or its label and click Sort Smallest to Largest. To get the records in reverse incremental order, right-click the control or its label and click Sort Largest to Smallest. Practical Learning: Introducing Sorting Records

1. From the resources that accompany our lessons, open the Altair Realtors2 database
2. In the Navigation Pane, double-click (the) Numbers and Dates (form) to open it
3. Click any field under Market Value
4. To see the list of properties from the cheapest to the most expensive, in the Sort & Filter section of the Ribbon, click the Ascending button
5. On the Ribbon, click Clear All Sorts
6. To view the list of properties from the oldest to the newest, right-click Year Built and click Sort Largest to Smallest
    
   
7. On the Ribbon, click Clear All Sorts

If you have columns that display numeric values, you can filter records with criteria that are based on those numbers and the types of numbers they have. You have many options. You can create a list of only records that use one particular value. You can create a list that includes numbers of a certain range. You can create a list that includes numbers lower than a certain value or numbers higher than a certain value.

To filter records that are exactly equal to a certain value:

• You can right-click the desired value under the column header for a table or a query, or right-click the value in a control or the label of that control for a form, then click the Equals option
• Click the value on a table, query, or form (for a form, you can also click the label of the control). Then, in the Sort & Filter section of the Ribbon, click Selection and click the Equals option

After clicking, the table or query would display only the records that share the value in the field.

Instead of getting the records that use the same value for a column, you may want the records that use values other than a particular one. To create such a list:

• You can right-click the desired value under the column header for a table or a query, or right-click the value in a control or the label of that control for a form, then click the Does Not Equal option
• Click the value on a table, query, or form (for a form, you can also click the label of the control). Then, in the Sort & Filter section of the Ribbon, click Selection and click the Does Not Equal option

To use the Equals option, you must click the exact value you want to use as the basis for comparison. Some times, you will need to act on a range of values. To do this, you can right-click any value under a column for a table or query, or a control or its accompanying label on a form, then position the mouse on Number Filters and select from the menu that appears. Here is an example:

As seen for strings, a dialog box would appear. What you do in the Custom Filter dialog box depends on a few factors. If you had selected:

• Equals...: You can type an exact number or a Boolean expression:
  • You can type a constant number and get the same result as if you had used the Equals option after right-clicking that number in the list
  • You can use a Boolean operator to perform a comparison. This means that you can use any of the comparison operators we reviewed in Lesson 17. For example, to get a list of numbers higher than 1000, you would select Equals and type >100
     
    
     
    
• Less Than...: You must type an exact value. For example, instead of using the Equals... option with the < operator, this option allows you to enter a value. Microsoft Access would consider the records whose values of that column are lower than the value you typed. For example, if you select Less Than and type 100, you would get the same result as if you have typed <100 with the Equals option. To get the records that have a value less than or equal to a certain value, you have two options:
  • You can right-click the desired value in the table, query or form and click the Less Than Or Equal To option
     
    
  • You can right-click any cell under the column or right-click the control or its label on a form, position the mouse on Number Filters, and click Equals. Then, in the Custom Filters dialog box, type <= followed by a number

• Greater Than...: You must type an exact value. For example, if you type 100, Microsoft Access would create a list of records whose values of that column are higher than the value you typed.

  To get the records that have a value greater than or equal to a certain value, you have two options:

  • You can right-click the desired value in the table, query or form and click the Greater Than Or Equal To option
  • You can right-click any cell under the column or right-click the control or its label on a form, position the mouse on Number Filters, and click Equals. Then, in the Custom Filters dialog box, type >= followed by a number
• Between...: If you click Between, a dialog box with two text boxes would come up:
  
  
  
  This option allows you to specify a range of values. In the top text box, you can type a small value. In the bottom text box, type a value equal or greater than the other. When you click OK, Microsoft Access would create a list of records in that range. Here is an example:
  
  
   
  
   
  You can also use the Equals option to get the same result. You would use the AND operator. Here is an example:
   
  
   
  
   
  We will study the AND operator in the next lesson but you should know that it is available.

1. To see the list of only properties built in 2000, under the Year Built column, right-click 2000 and click Equals 2000
    
   
    
   
2. Right-click anywhere under Year Built and click Clear Filter From Year Built
3. To get a list of properties that have more than 1 bathroom, right-click any cell under Bathrooms, position the mouse on Number Filters and click Greater Than
4. In the dialog box, type 1.50
    
   
5. Click OK
    
   
6. Click the Filtered button

Dates and times represent valuable items of a database. Although they mostly use numbers, their values represent spatial occurrences and therefore are treated accordingly. As done for strings and numbers, fields with date and time values can be sorted. For date values, the results of sorting produce a chronological or a reverse chronological order. Because a date (or time) is a spatial value, it can be considered as occurring:

• Before a specific date (or time). This would consider a date as a constant value
• At a specific date (or time). This would consider a date as a constant value
• After a specific date (or time). This would consider a date as a constant value
• Between two specific dates (or times). This would consider a range of date (or time) values

Sorting the Records

As we saw in Lesson 18, there are various formats and rules for date and time values. Consider the following table:

When it comes to dates and times, you can sort values. When Microsoft Access is asked to sort dates or times, it refers to the Regional and Language Settings of Control Panel. This means that the rules may be different from one language to another.

To sort the records of a table, query, or form, first identify the column or control you will use. On the table or query, you can click a field under the column of your choice. On a form, you can click a control or its accompanying label. Then:

• Click the down pointing arrow on the right side of the name of the column and click Sort Oldest to Newest
• In the Sort & Filter section of the Ribbon, click the Ascending button

You can also right-click to sort date or time-based fields. Right-click the column header or a field under the column and click Sort Oldest to Newest

If the column or control contains empty values, they would appear first. After the empty records, the oldest record would show. The list would end with the record with the most recent value. As mentioned for the sorting of other types, each record is kept with its values for each column. Therefore, when the records are sorted, Microsoft Access first refers to the field you selected and displayed the other values of the corresponding records in their fields. If two records of a column have the same values, they are displayed in the same range and the records of the next column are sorted:

This scenario also applies to time-based columns.

As opposed to a chronological order, you can also sort records in reverse chronological order. To do this:

• Click a column header or a cell under a column header for a table or query, or click a control or its accompanying label on a form. Then,
  • Click the down pointing arrow on the right side of the name of the column and click Sort Newest to Oldest for a table or a query
  • In the Sort & Filter section of the Ribbon, click the Descending button
• Right-click a column header or a cell under a column header for a table or query, or right-click a control or its accompanying label on a form, and click Sort Newest to Oldest

As mentioned already, after sorting a column, the corresponding values display on its left and right. If two records have the same value for a column, they are listed and Microsoft Access sorts the records of the next column in chronological order. If you want, you can sort the records of the next column in reverse chronological order. The first records sorted chronologically would keep their sequence and the records in the new column would be sorted:

This description applies to time-based column also.

1. Click any field under Date Listed
2. To see the list of properties by the listed date, in the Sort & Filter section of the Ribbon, click the Ascending button
    
   
3. In the Sort & Filter section of the Ribbon, click the Clear All Sorts button

A date or a time can be considered either as a constant value or as a range of values. When examining the values of a list, you may want to include only some values and/or to exclude some others. This means that you can filter values based on a date, a time, or even a combination of both. To filter the records on a table, a query, or a form, once again you have various options. To get a list of records that occur at the same date or time, you can:

• Right-click the column and click the Equals option
   
  
• Click the value. In the Sort & Filter section of the Ribbon, click Selection and click the Equals option

After clicking, the table or query would display only the records that share the (exact) same date or time in the field:

Filtering by (Only) a Specific Month, Day, or Year

Instead of a whole date or time, you can filter using only a specific day of all months, a specific month of all years, or a specific year in all records. These depend on how the values are entered and/or how the values display (or are accessed). For example, if a date-based column is configured to display its values in short date (Format = Short Date):

• To get the list of records that share a month regardless of the day or month, you can select and right-click the first number in the field (remember, this has to do with the way the computer is configured to handle or display dates; the description here is conform to US English). For example you can select the number 1 on the left side of the left backslash; this would represent January. Then:
  • Right-click that selected number and click the Begins With option:
     
    
  • In the Sort & Filter section of the ribbon, you can click Selection, and click the Begins With option
     
    The resulting list would include only records that occur on the selected month:

• To exclude the records that occur on a specific month, you can select and right-click the first number in the field. Then:
  • Right-click that selected number and click the Does Not Begin With option:
  • In the Sort & Filter section of the ribbon, you can click Selection, and click the Does Not Begin With option
     
    The resulting list would include all records that do not occur on the selected month
• To get the list of records that share the same day regardless of the month or year, you can select and right-click the number between the forward slashes in the field. Then:
  • Right-click that selected number and click the Contains option:
     

• In the Sort & Filter section of the ribbon, you can click Selection, and click the Contains option
   
  The resulting list would include all records that occur on that day regardless on their year or month:

Instead of selecting the number for the day, if you are working on records that involve today's date, you can filter by today, yesterday or tomorrow. To do this, right-click a date in a field or an empty field that is supposed to hold a date value, position the mouse on Date Filters and select from the list:

It is helpful to remember that yesterday, today, and tomorrow each has an exact (referred to as constant) date. The user will know this date when performing the filtering operation

• To exclude the records that occur on a specific day regardless of the month or year, you can select and right-click the number between the forward slashes in the field. Then:
  • Right-click that selected number and click the Does Not Contain option
  • In the Sort & Filter section of the ribbon, you can click Selection, and click the Does Not Contain option
     
    The resulting list would include all the records that do not occur on that day

• To get the list of records that share the same year regardless of the day or month, you can select and right-click the number on the right side of the right forward slash in the field. Then:
  • Right-click that selected number and click the Ends With option
  • In the Sort & Filter section of the ribbon, you can click Selection, and click the Ends With option
     
    The resulting list would include all records that occur on that year
• To exclude records that occur in a certain year, you can select and right-click the number on the right side of the right forward slash in the field. Then:
  •  Right-click that selected number and click the Does Not End With option
  • In the Sort & Filter section of the ribbon, you can click Selection, and click the Does Not End With option
     
    The resulting list would include all records that do not occur on that year

1. Navigate to the second record and, in the Date Listed field, double-click 2008 to select it
    
   
    
   Notice that the Record Number indicates 2 of 28
2. On the Ribbon, click Selection and click Ends With 2008.
   Notice that the Record Number displays 1 of 15
3. Under the form, click the Filtered button

If you are working on a series of records that use the same values as your current week, your current month, your current quarter, or your current year, you can use one of these values as a base to filter. To do this, you can right-click a date in a field or an empty field for a date, position the mouse on Date Filters and select from the list:

Once again, remember that when you select an option, Microsoft Access refers to the system clock of the computer on which the operation is being performed:

Filtering a Range of Dates or Times

If you do not know the exact date you want to filter by, you can/should consider working on a range of dates. You have many options. To start, you can right-click any field under the column of a table or query, or right-click a control or its accompanying label on a form, position the mouse on Date Filters and choose one of the options. If you click Equals..., Does Not Equal..., Before..., or After... option, the Custom Filter dialog box would come up. To assist you with filtering and to select an exact date, the Custom Filter dialog box is equipped with a picture button that, when clicked, would display a calendar:

When using the Custom Filter dialog box for a date-based column, you can type an exact date or use the calendar to select a date, which would reduce the likelihood of mistakes. If you are working on a time-based field, then you must type a valid time value in standard or military format.

Consider the following table of customers staying in a hotel:

If you right-click a cell under Reservation or right-click a control for the reservation date, position the mouse on Date Filters and click:

• Equals...: You can type a date in a valid format or select using the calendar, or you can type a specific but valid date or time:
  • If you specify a constant date or time, this would be the same as if you had right-clicked the same date or time and had clicked the Equals option
  • If you do not know the exact date or time, you can use a comparison operator and apply it on a date or time. Here is an example that uses <=2/12/2008 to find records that occur on or before February 12, 2008::
     
    

 
In the same way, you can use any of the logical operators to find the records that occur on a certain date or time (simply enter the date or time), before (<) a certain date or time, before or on (<=) a certain date or time, after (>) a certain date or time, after or on (>=) a certain date or time, or not (<>) on a certain date or time

• Does Not Equal...: You must enter an exact date or time in a valid format and not use a logical operator. After clicking OK, Microsoft Access would create a list of records that do not use the value. For example, if you type 2/12/2008, Microsoft Access would find all records that do not occur on that date.
  This is equivalent to using <>date with the Equals option
• Before...: You must type a specific date or time and not use logical comparison operators. After entering a date or time and clicking OK, Microsoft Access would create a list of records that occur prior to the specified date or time, including that date or time.
  This is equivalent to using <=date with the Equals option
• After...: You must type a constant value for a date or a time; do not apply a logical comparison. After entering a date or time and clicking OK, Microsoft Access would create a list of records that occur after and on the specified date or time.
  This is equivalent to using >=date with the Equals option

If you right-click a cell under a column or right-click a control or its accompanying label for a date, position the mouse on Date Filters and click Between... Alternatively, you can click a date-based field. Then, on the Ribbon, click Selection and click Between. In both cases, the Between Dates dialog box with two text boxes would display. In each text box, you can type a specific date or add a comparison operator to a date or time. This means that you can enter an exact date for each text box. Here is an example:

When you click OK, Microsoft Access would look for records that occur between both dates:

Notice that, on the Between Dates dialog box, the first label uses the caption Oldest and the other text box uses the Newest label. This is used only as a guide. You can enter a newer date in the first text box and an older date in the second text box:

You would get the same result. Although there is hardly any justification for that, you can use comparison operators in the text boxes, in none of the text boxes, or in one and not the other text boxes. Microsoft Access would apply the operators and use Boolean algebra to get the results.

Using the Between Dates dialog box is equivalent to using the logical AND operator that we will study in Lesson 25.

1. Right-click Date Listed, position the mouse on Date Filters and click After...
2. Click the calendar button and select the date corresponding to March 22, 2008
3. Click OK
    
   
4. Right-click the Date Listed: label and click Clear Filter From Date Listed
5. Click Date Listed: label
6. On the Ribbon, click Selection, and click Between...
7. In the Top text box, type 06/06/07
8. In the other text box, type 06/06/08
9. Click OK to see the result
10. On the Ribbon, click Toggle Filter (or Remove Filter)
11. Close the form. If asked whether you want to save, click No

As seen in Lesson 13, a Boolean field is one that holds a true or false result and it is created as a Yes/No type. By default, and most of the time, a Boolean field displays a check box that can be checked (or marked) or cleared (or unchecked). By its Boolean interpretation, Microsoft Access considers that a check box that is checked is "selected" and a check box that is not checked in "cleared". You can use this logic to sort the records of a table, a query, or a form.

You can sort the Boolean records as selected or cleared. As it should seem obvious, when you sort in selected order, the checked boxes would come up first. To sort the Yes/No records of a table, a query, or a form, in selected order, you can:

• Right-click the column header or a check box under the column header on a table or a query, or right-click the check box or its accompanying label on a form, and click Sort Selected to Cleared
• Click the down-pointing button on the right side of the name of the column and click Sort Selected to Cleared
   
  
• Give focus to the check box but do not click. To give focus to a check box without clicking,
  • On a table or a query, if there is a cell on the left column to the check box, click that cell and press Tab
  • On a table or a query, if there is no cell on the left side of the check box but there is a column on the right side with a cell, click that cell and press Shift + Tab
  • On a table or a query, click any cell other than the one with the check box you want to give focus to. On a form, click any control or label other than the one with the check box you want to give focus to. Press Tab to move up the index sequence or Shift + Tab to move down the index sequence, until the check box receives focus.
    
    In all cases, you will know that the check box has focus when it is surrounded with a dotted square line.

  Once the check box has received focus, in the Sort & Filter section of the Ribbon, click the Ascending button

To sort the Boolean records in cleared order:

• Right-click the column header or a check box under the column header on a table or a query, or right-click the check box or its accompanying label on a form, and click Sort Selected to Cleared
• Click the down-pointing button on the right side of the name of the column and click Sort Cleared to Selected
• Give focus to the check box and, in the Sort & Filter section of the Ribbon, click the Descending button

To put the list back in the sequence it previously had, in the Sort & Filter section of the Ribbon, click the Clear All Sorts button .

Practical Learning: Introducing Sorting Records

1. In the Navigation Pane, double-click Properties: Table
2. To re-arrange the list of properties starting with those with a finished basement, right-click Finished Basement and click Sort Selected to Cleared
    
   
3. On the Ribbon, click Clear All Sorts
4. Press the left arrow key to remove focus from the Finished Basement column

Filtering Boolean Records

If you have a column created as a Yes/No type, you can isolate the list with only checked or only cleared fields. To filter records that are checked:

• Right-click a check box (checked or not) under the column and click Is Selected
   
  
• Click the column header or give focus to a check box under it. In the Sort & Filter section of the Ribbon, click Selection and click Is Selected

After clicking, the table, query, or form would display only the records that have that field checked.

To filter records by the cleared check boxes:

• Right-click a check box (checked or not) under the column and click Is Not Selected
• Click the column header or give focus to a check box under it. In the Sort & Filter section of the Ribbon, click Selection and click Is Not Selected

After clicking, the table, query, or form would display only the records that have that field cleared.

Practical Learning: Filtering Records

1. To see the list of properties that do not have a garage, right-click any check box under Indoor Garage and click Is Not Selected
    
   
2. Close the table
3. When asked whether you want to save the table, click No
4. In the Navigation Pane, under Properties: Table, double-click Properties
5. To review the list of properties that have a finished basement, right-click Is the Basement Finished? and click Is Selected
    
   
6. Close the form

Exercises  

Yugo National Bank

1. Open the Yugo National Bank1 database
2. Open the Customers table and sort the records by the date created
3. Do not save the table

Watts A Loan

1. Open the Watts A Loan1 database
2. Open the Customers form and show only the accounts that were in 2008 on April 15

World Statistics

1. Open the World Statistics1 database
2. Open the Countries form and show only the countries that got their independence in 1960
3. Show the countries that have their national holiday in July
4. Do not save anything

US Senate

1. Open the US Senate1 database
2. Open the Senators form and make it form show only the senators who were elected between 2002 (included) and 2006 (included). Do not save the form