Microsoft KB Archive/275108

= BUG: ODBC call fails when filtering a decimal field in Access 2002 =

Article ID: 275108

Article Last Modified on 8/11/2004

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q275108





Advanced: Requires expert coding, interoperability, and multiuser skills.

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



SYMPTOMS
If you run a query or a filter on data from a Microsoft SQL Server-based table, you receive the following error message:

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]Line1: Incorrect Syntax near &quot;,&quot;.(#170)



CAUSE
This behavior occurs because Microsoft Access does not substitute a period for a comma in a query created on a computer on which the comma option is selected as the decimal separator under Regional Settings.



RESOLUTION
To work around this issue, wrap the value in a data type conversion function, such as CDbl or CSng. The data type conversion function that you select should closely correspond to the data type in the table. For example: SELECT dbo_TableXYZ.Field1, dbo_TableXYZ.Field2 FROM dbo_TableXYZ WHERE (((dbo_TableXYZ.Field2)=CDbl(1.5)));



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



MORE INFORMATION
CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

This issue affects any SQL Server data type that allows decimal data, such as the Float, Real, Money, SmallMoney, and Numeric data types.

Steps to Reproduce the Problem
 Start SQL Server Query Analyzer and connect to your SQL Server computer.  Type or paste the following T-SQL code in the right pane of the Query Analyzer: USE Northwind GO CREATE TABLE TableXYZ ( Field1 char(3) primary key NOT NULL, Field2 decimal (18,2) NULL ) GO INSERT INTO TableXYZ (field1, field2) VALUES ('abc', 1.5) GO  On the Query menu, click Execute, or press F5 to run the query.

This action creates TableXYZ in the Northwind database and inserts one row of data. Click Start, point to Settings, click Control Panel, and then double-click Regional Settings.</li> On the Number tab, change the Decimal symbol setting to comma, and then click OK.</li> Create an ODBC data source (DSN) for the Northwind database on the SQL Server computer that you used in step 1.</li> Start Microsoft Access and open any database file (.mdb).</li> Link the TableXYZ table from SQL Server to your Access database by using the DSN that you created in step 6.</li>  Create a new query with the following SQL syntax: SELECT Field1, Field2 FROM dbo_TableXYZ WHERE dbo_TableXYZ.Field2 = 1.5 </li> On the Query menu, click Run.

Notice that the query fails and you receive the error message described in the &quot;Symptoms&quot; section.</li></ol>

<div class="references_section">