Comma as a decimal symbol (Regional and Language settings)

[Peter]

Here's a tough one:
For the US among others the decimal symbol is a dot. Many countries, mine
with them, is using a comma instead. Now, when performing an SQL query with
a decimal number as an argument with the decimal symbol being a comma, the
query will fail: "Syntax error (comma) in query expression"
If I go to Control Panel -> >Regional and Language Options->Customize and
change the comma to a dot, the query will work as expected. However, running
the application on another computer would require this change as well. I'd
like to avoid Quick and Dirty solutions. I've tried to manually enter a dot
as the separator but Access just removes it making 2.3 become 23.
I'm using US English MS Access 2003 with US English XP SP2, always upgraded
with the lastest hotfixes. Is there a way to make Access honour the Regional
settings (Swedish)?
Regards
Peter

 

[Matthias Klaey]

Here's a tough one:
For the US among others the decimal symbol is a dot. Many countries, mine
with them, is using a comma instead. Now, when performing an SQL query with
a decimal number as an argument with the decimal symbol being a comma, the
query will fail: "Syntax error (comma) in query expression"
If I go to Control Panel -> >Regional and Language Options->Customize and
change the comma to a dot, the query will work as expected. However, running
the application on another computer would require this change as well. I'd
like to avoid Quick and Dirty solutions. I've tried to manually enter a dot
as the separator but Access just removes it making 2.3 become 23.
I'm using US English MS Access 2003 with US English XP SP2, always upgraded
with the lastest hotfixes. Is there a way to make Access honour the Regional
settings (Swedish)?
Regards
Peter

Hi Peter

Use the Str function:

Dim dblNum As Double

dblNum = 2.3
"SELECT * FROM MyTable WHERE MyNum <= " & Str(dblNum)

The STR() function converts the number to a string with the dot as
decimal symbol regardless of the Regional Settings.

HTH
Matthias Kläy