Home

Sorting Date and Time-Based Fields

 

Introduction

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:

Hotel Management

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 Ascending

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 Descending
  • 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.

 

Home Copyright © 2008-2012 FunctionX