Decimal seperator between different languages

[seshan]

Hi,
I am forming a Dynamic SQL using a decimal number in a different Regional language, which has comma as a decimal separator. How do I convert the decimal number to the format that can be used inside a query.

Thanks in advance

[ChrisO]

Dim MyNumber As Double

MyNumber = 123.456

MsgBox Replace(CStr(MyNumber), ",", ".")

[seshan]

How do we identify the decimal separator in VBA when we use a lot of languages?

What is the purpose of Str function? Can it be used for this purpose. When we wrap a decimal number whose decimal separator is comma with an Str function, the decimal seperator appears to be a period. Is it doing a conversion internally?

[ChrisO]

You do not need to identify the decimal separator, simply replace it with a period if it is there.

The Replace function works on a string argument so if we feed it a number Access will convert it to a string and then process it.

It’s when Access converts it to a string that it looks at the regional settings and inserts what ever is set as the regional decimal separator.

So let’s say we have a number say 1.2 (always a period as a number) and we convert it to a string with say English(US) regional settings we get 1.2 (with a period)
however if we are using German (Germany) settings we get 1,2 (with a comma)

If we build an SQL string such as: -
"Select * From Blah Where ID = " & MyNumber

With English(US) regional settings we get: -
Select * From Blah Where ID = 1.2
And it works.

But with German (Germany) settings we get: -
Select * From Blah Where ID = 1,2
And it fails.

If we use: -
"Select * From Blah Where ID = " & Replace(MyNumber, ",", ".")
We get: -
Select * From Blah Where ID = 1.2
And it works with both regional settings.

Try testing it and change your regional settings to German (Germany).