Insert statement fails with new Number format

[Kowalski]

Hi
I upgraded my system to Windows 8 and Office 2013, and it's now 64 bit instead of 32 bit as before. Also note that I'm still using Access 2010 as before.
So the following problem now arise in this new config that was not there before.

When you do a calculation like '332.16 - 1', the answer is now 331,16, instead of 331.16. Note the point are now replaces with a comma.

Might sound trivial, but everywhere in my code where is have a dynamic SQL like:
Currentdb.EXECUTE "INSERT INTO Tasks (TaskID, MyNumber)
VALUES ("Tsk123", Var1 - Var2)",
the result was always something like:
INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331.16), but now it is:
INSERT INTO Tasks (TaskID, MyNumber) VALUES ("Tsk123", 331,16), so it is seen as an extra value and the statement fails.

So, yes I can fix this one statement, but I have multiple occurrences of this throughout my app. How do I fix this?

And changing something in my Control Panel - Regional Settings would not be the answer, as on all the client PC's it might be different.

 

[spikepl]

In SQL statements you need to use the US format for decimal numbers. When writing SQL from VBA, wrap decimal numbers in function Str. Str uses "." as decimal separator, irrespective of country settings.

'with "," set as decimal separator
myValue=5,5
Str(myValue) yields "5.5"

'with "." set as decimal separator
myValue=5.5
Str(myValue) yields "5.5"


Update:
The emergence of the above issue is caused by change in regional settings. I do not quite understand that there was no problem before - I presume all new system installations are at the same physical locations as before. If people now have a new decimal separator, then they will also suffer in all other office applications.

 

[Kowalski]

Thanks.
Problem is just that this needs to be done throughout the whole app in hundreds of locations. Bummer.
I really hoped that there will a simpler solution.

 

[spikepl]

Again , I find the entire issue mysterious. If "hundreds of locations" now have a new regional setting, then this will affect any user interaction, because now "," is used as decimal separator. Some CSV files will probably be unreadable. "," will appear in spreadsheets ...etc etc I just cannot imagine a system upgrade accompanied by a change of regional settings taking place in an existing organisation. But then ... many things I cannot imagine ...