Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 207700 - Last Review: July 15, 2004 - Revision: 1.1
ACC2000: Query with Time Criteria Returns No Records from Microsoft SQL Server
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).
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.
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:
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'})
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Â
(http://kbalertz.com/Feedback.aspx?kbNumber=209645/EN-US/
)
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([<Name of Time Field>],"hh:mm:ss AM/PM"))
You can then enter the literal time value enclosed in number signs (#) on
the
Criteria row of this expression.
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.
- On the File menu, point to Get External Data, and then click Link Tables.
- In the Link dialog box, click ODBC Databases in the Files of type 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 logon information, and click OK.
- In the Link Tables dialog box, select the tblTimeTest table that you 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 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#
- Close and save the qryTest query that you created in step 8.
- Run the qryTest query. Note that Microsoft Access returns an empty recordset.
For more information about specifying the data type of query parameters, click
Microsoft Access Help on the
Help menu, type
specify the data type of a parameter in a parameter query in the Office Assistant or the Answer Wizard, and then click
Search to view the topic.
APPLIES TO
- Microsoft Access 2000 Standard Edition
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate