ACC95: Parameter Query Excludes Ending Date (149945)


The information in this article applies to:

  • Microsoft Access for Windows 95 7.0
This article was previously published under Q149945
Moderate: Requires basic macro, coding, and interoperability skills.

SYMPTOMS

When you use a Date/Time field as a parameter within a parameter query, where both valid dates and times have been stored within the Date/Time field, some of the expected records may not appear in the recordset when you type only dates (excluding time values)into the parameter box when prompted.

RESOLUTION

To work around this behavior, enter 11:59:59pm as part of the parameter value when entering a date into the parameter box prompt, or enter the day you want plus one. For example, if you want 07/31/93, enter a date of 08/01/93 when prompted for a date.

Another approach would be to create a field expression within the parameter query that would parse the time from the Date/Time field. For example:

Expr1: CVDate(Int([<DateTimeFld>]))


where <DateTimeFld> would be replaced by the name of the actual Date/Time field within the table.

MORE INFORMATION

When you simply store a date (excluding the time) into a Date/Time field, the field will assume a default time value of 12:00:00am.

If the following were entered as the query's criteria:

Between [<param1>] and [<param2>]


the recordset would not include records that have a time value greater that 12:00:00am if only a date is entered into <param2>. When only a date is entered into <param2>, Microsoft Access will always assume a time value of 12:00:00am. Although many records may have a date that matches the date entered into <param2>, if the date field also stores a time value greater than 12:00:00am, that record will be excluded from the recordset.

Steps to Reproduce Behavior

This example assumes that the sample database Northwind.mdb has not been previously altered.
  1. Open the sample database Northwind.mdb.
  2. Create a new query in Design View and add the Orders table.
  3. Drag the OrderDate field to the QBE grid and set Sort to Ascending.
  4. Enter the following criteria into the OrderDate column:

    Between [Start Date] And [End Date]


    NOTE: It is necessary to define the data type of both [Start Date] and [End Date] by clicking Parameters on the Query menu.
  5. Click Run on the Query menu and enter the following values when prompted for the Start Date and End Date:

    08/01/93

    08/31/93

  6. Move to the very last record, which should have an OrderDate value of 08/31/93 and add "12:00:01am" to the field's current date value. The new OrderDate value should look like as follows:

    08/31/93 12:00:01am

  7. Click Query Design on the View menu and once in Design view, click Run on the Query menu to rerun the query.
  8. When prompted, use the same values you used in step 5.

    NOTE: Because only a date was entered into the [End Date] prompt, Microsoft Access assumes a default time of 12:00:00am. The results of this new recordset drops the record that has a date of 08/31/93 because it now contains a time value greater than 12:00:00am.
Modification Type:MajorLast Reviewed:7/5/2002
Keywords:kbprb kbusage KB149945
©2002 Microsoft Corporation. All rights reserved.