Microsoft KB Archive/173097

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

Article ID: 173097

Article Last Modified on 1/20/2007

-

APPLIES TO


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

-



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.



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



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 more information about the query-by-form technique, please see the following article in the Microsoft Knowledge Base:

95931 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([],"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.



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 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) </li> <li>Start Microsoft Access and create a new database.</li> <li>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.</li> <li>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.</li> <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 log on information, and click OK.</li> <li>In the Link Tables dialog box, select the tblTimeTest table created in Step 1, and then click OK.</li> <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> <li> Create the following 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> <li>Close and save the qryTest query that you created in Step 8.</li> <li>Run the qryTest query. Note that Microsoft Access returns an empty or null recordset.</li></ol>

<div class="references_section">