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.

Back to the top

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 P.M. into the table, the following calls are made by the SQL Server ODBC driver:

   SQLPrepare :

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

   SQLBindParam:

      12:35:00
				

Microsoft SQL Server then converts the value 12:35:00 to the following:

   1899-12-30 12:35:00.00
				

When you run a query in which the Criteria row for the Time field contains a literal value such as #12:35:00 PM# against this linked table, the SQL Server ODBC driver sends the following command to the SQL Server:

   SQLExecDirect:

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

Back to the top

Use either of the following methods to resolve this problem.

Back to the top

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]
				

For more information about the query-by-form technique, please see the following article in the Microsoft Knowledge Base:

Back to the top

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"))
				

NOTE: If you are using Microsoft Access version 2.0, be sure to type the alias Expr1: along with the rest of the expression to avoid receiving a syntax error message.

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

Back to the top

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Back to the top

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

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

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

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

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

Back to the top