AnsweredAssumed Answered

ExecuteSQL returns wrong format for decimal numbers with international system settings

Question asked by gcatnine on Jun 26, 2013
Latest reply on Mar 14, 2014 by TSGal

Summary

ExecuteSQL returns wrong format for decimal numbers with international system settings

Product

FileMaker Pro

Version

12.0v3, 12.0v4

Operating system version

Windows

Description of the issue

When using ExecuteSQL to retrieve a number field, I’m expecting to get the number formatted as the international system settings in use.
That is: if the decimal point is “,” (comma) I’m expecting that the number returned is, for instance: 12,3.
Instead the ExecuteSQL returns always  the US number format, in our case 12.3 (with point instead of comma).
That causes that all calculations are wrong. As a matter of fact, the point is not considered when you have a comma as decimal separator, so in our case the number becames 123 (hundred twenty three).

Steps to reproduce the problem

Use a Windows system with number format that has:
Decimal point: “,” (comma)
Digit separator: “.” (point)

Create a new file or use the File Option Data Entry setting to use the System Format
Put a decimal number in a numeric field and control that is displayed with the comma.
Execute an ExecuteSQL selecting that field and you will see that is returned with the point.

Expected result

Decimal number formatted with the comma and not with the point, in order to be recognized correctly by FM

Actual result

Decimal number formatted with the point and not with the comma, and is not recognizes correctly by FM

Exact text of any error message(s) that appear

NA

Configuration information

Windows7 and 8
FM 12.0v3 and 12.0v4 advance
FM Server advance 12.4

Workaround

create a function to change the point with a comma

Outcomes