Home

Filtering Date and Time-Based Fields
 

Filtering by a Specific Date or Time

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:
       
      Hotel
    • 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:

    If you select Microsoft Access
    Today Finds all records that occur on today's date
    Yesterday Finds all records that occurred the day before today
    Tomorrow Finds all records that will occur tomorrow

    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
 

Filtering Records Related to the Current Date

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:

If you select Microsoft Access
This Week Finds all records entered for the current week, not the last few days and the next few days. Microsoft Access uses the rules of the language of the computer. For example, in US English, the week starts on Sunday and ends on the following Saturday. If you are on Friday and select This Week, Microsoft Access would consider records from Sunday of this week until tomorrow Saturday
Last Week Finds the records that were entered for last week, not the last 7 days. Microsoft Access uses This Week as the basis
Next Week Finds all records that have been entered for next week, not for the next 7 days
This Month Finds all records that have been entered for this month
Last Month Finds all records that exist for last month, not the last 30 days
Next Month Finds all records that exist for next month, not for the next 30 days
This Quarter Finds all records that have been entered for this quarter
Last Quarter Finds all records that have been entered for last quarter
Next Quarter Finds all records entered for the next quarter
This Year Finds all records entered for this year
Last Year Finds all records entered for last year
Last Year Finds all records entered for last year
Year To Date Finds all records that occur from the beginning of this year to now. Remember that to determine now, Microsoft Access refers to the system clock

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:

Custom Filter

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:

Consider the following table of customers hotel staying records

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:

Between Dates

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.
Home Copyright © 2008-2016, FunctionX, Inc.