ACC: Query w/Time Criteria Returns No Records from MS SQL Server
ID: Q173097
|
The information in this article applies to:
-
Microsoft Access versions 2.0, 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
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 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 more information about the query-by-form technique, please see the
following article in the Microsoft Knowledge Base:
Q95931 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([<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 Critera row of this expression.
STATUS
Microsoft has confirmed this to be a problem in Microsoft Access.
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 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 query qryTest created in Step 8.
- Run qryTest. Note that Microsoft Access returns an empty or null
recordset.
REFERENCES
For more information about specifying the data type of query parameters,
search the Help Index for "data types, parameter queries."
Additional query words:
linking attaching
Keywords : kbinterop QrySqlvw QryParm
Version : WINDOWS:2.0,7.0,97
Platform : WINDOWS
Issue type : kbbug
Last Reviewed: April 17, 1999