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:
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.
- On the Number tab, change the Decimal symbol setting to comma (,), and then click OK.
- Create an ODBC data source (DSN) for the Northwind database on the SQL Server computer that you used in step 1.
- Start Microsoft Access and open any database file (.mdb).
- Link the TableXYZ table from SQL Server to your Access database by using the DSN that you created in step 6.
Create a new query with the following SQL syntax:
SELECT Field1, Field2 FROM dbo_TableXYZ WHERE dbo_TableXYZ.Field2 = 1.5- On the Query menu, click Run.
Notice that the query fails and you receive the error message described in the "Symptoms" section.
REFERENCES
For more information about creating ODBC data sources, click Microsoft Access Help on the Help menu, type set up odbc data source in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about data type conversion functions, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type type conversion functions in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about the SQL Query Analyzer, refer to the following MSDN Web site:
Additional query words: pra
Keywords: kberrmsg kbtshoot kbbug kbnofix kbdatabase kbdesign kbquery KB275108
