ACC95: Parameter Query Excludes Ending Date

ID: Q149945

The information in this article applies to:

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

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.



Keywords          : kbusage QryParm 
Version           : 7.0
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: April 23, 1999