United States

Change

All Microsoft Sites

Help and Support

Article ID: 173097 - Last Review: January 20, 2007 - Revision: 3.1

ACC: Query with Time Criteria Returns No Records from Microsoft SQL Server

Retired KB Content Disclaimer

View products that this article applies to.

This article was previously published under Q173097

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

SYMPTOMS

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

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

RESOLUTION

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:

95931 (http://support.microsoft.com/kb/95931/EN-US/ ) ACC: How to use the Query-by-Form (QBF) Technique

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

STATUS

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

MORE INFORMATION

Steps to Reproduce Behavior

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)

Start Microsoft Access and create a new database.
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.
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.
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.
In the Link Tables dialog box, select the tblTimeTest table created in Step 1, and then click OK.

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

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

Create the following 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#

Close and save the qryTest query that you created in Step 8.
Run the qryTest query. Note that Microsoft Access returns an empty or null recordset.

Back to the top

REFERENCES

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

Back to the top

APPLIES TO

Microsoft Access 2.0 Standard Edition
Microsoft Access 95 Standard Edition
Microsoft Access 97 Standard Edition

Back to the top

kbbug kbinterop KB173097

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top