ACC: Query w/Time Criteria Returns No Records from MS SQL Server

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

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

When you run a query based on a linked (attached) Microsoft SQL Server table that contains a Date/Time field, and the criteria for the Date/Time field contains a literal time value, Microsoft Access returns an empty result set.

CAUSE

When a linked SQL Server table contains a field with a data type of Date/Time, and you insert a time value such as 12:35:00 PM into the table, the following calls are made by the SQL Server ODBC driver:

   SQLPrepare :
      INSERT INTO "dbo"."tblTimeTest" VALUES (?)

   SQLBindParam:
      12:35:00

   1899-12-30 12:35:00.00

   SQLExecDirect:
     SELECT "dbo"."tblTimeTest"."TimeTest" FROM "dbo"."tblTimeTest" WHERE _
         ("TimeField" = {t '12:35:00'})

RESOLUTION

You can resolve this problem by using one of the following methods:

Method 1

Create a parameter query in Microsoft Access. You can use the query-by-form technique and specify a control on a form as a parameter; you can also define the parameter in the query itself and specify its data type as Date/Time. For example:

   Field: TimeField
   Criteria: [Enter Time]

   ARTICLE-ID: Q95931
   TITLE     : ACC: How to use the Query-by-Form (QBF) Technique

Method 2

Create the following expression in the query to extract the time portion of the field:

   Expr1: CVDate(Format([<Name of Time Field>],"hh:nn:ss AM/PM"))

You can then enter the literal time value enclosed in number signs (#) on the Critera row of this expression.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

  1. In a Microsoft SQL Server utility (such as isql/w), run the following
     commands:

       create table tblTimeTest (ID int, TimeField datetime)
       go
       create unique index tblTimeTest_ndx on tblTimeTest (ID)

  2. Start Microsoft Access and create a new database.

  3. On the File menu, point to Get External Data, and then click Link
     Tables. If you are using Microsoft Access version 2.0, click Attach
     Table on the File menu.

  4. In the Link dialog box, click ODBC Databases in the Files Of Type box.
     If you are using Microsoft Access version 2.0, click <SQL Database>
     in the Data Source box.

  5. In the Select Data Source dialog box, click the data source to connect
     to your SQL Server database, and then click OK. Supply any necessary
     log on information, and click OK.

  6. In the Link Tables dialog box, select the tblTimeTest table created in
     Step 1, and then click OK.

  7. View the table in Datasheet view. Add the following values to the
     table:

        ID   TimeField
        --  ----------
        1    12:35:00

  8. Create a new query based on the linked SQL Server table:

        Query: qryTest
        -------------------------
        Type: Select Query

        Field: ID
           Table: dbo_tblTimeTest
        Field: TimeField
           Table: dbo_tblTimeTest
           Criteria: #12:35:00#

  9. Close and save the query qryTest created in Step 8.

 10. Run qryTest. Note that Microsoft Access returns an empty or null
     recordset.

REFERENCES

For more information about specifying the data type of query parameters, search the Help Index for "data types, parameter queries."