Microsoft KB Archive/207700

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

Article ID: 207700

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207700



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

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you run a query based on a linked 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 recordset.



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 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'})



RESOLUTION
Use either of the following methods to resolve this problem.

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 additional information about the query-by-form technique, please click the article number below to view the article in the Microsoft Knowledge Base:

209645 ACC2000: 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([],"hh:mm:ss AM/PM"))

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



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



Steps to Reproduce Problem
  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.</li> On the File menu, point to Get External Data, and then click Link Tables.</li> In the Link dialog box, click ODBC Databases in the Files of type box.</li> 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 logon information, and click OK.</li> In the Link Tables dialog box, select the tblTimeTest table that you created in Step 1, and then click OK.</li>  View the table in Datasheet view. Add the following values to the table: <pre class="fixed_text">  ID   TimeField -- --    1    12:35:00                    </li>  Create a new query based on the linked SQL Server table: <pre class="fixed_text">  Query: qryTest -  Type: Select Query

Field: ID     Table: dbo_tblTimeTest Field: TimeField Table: dbo_tblTimeTest Criteria: #12:35:00# </li> Close and save the qryTest query that you created in step 8.</li> Run the qryTest query. Note that Microsoft Access returns an empty recordset.</li></ol>

<div class="references_section">