TextField, Format ###,###,###,##0.00 and number > 1000000

I have assigned the format ###,###,###,##0.00 to a textfield in the IDE. When I assign a value higher than 1’000’000 to this field then it gets formatted as expected, but when the field gets focus (the user clicks into it) , then the number is displayed in scientific notation.

Values up to 1 Mio are always displayed in a form readable by non-scientists, but from 1’000’001 onwards it automatically switches to scientific notation ( 1’000’001.00 = 1.000001e+6 ).

If I delete the format then the number is displayed as it was entered (but not formatted).

Is there a way to use format but to switch off this automatic conversion to scientific notation, when the number is higher than 1 Mio?

i think i have this problem too…

I start to think that this format function is not reliable.
For instance, in the docs it says :

[quote]# Placeholder that displays the digit from the value if it is present.
If fewer placeholder characters are used than in the passed number, then the result is rounded.[/quote]

Format String of TextField: -###,###,###,###,##0.00

Number typed into textfield: 1234567.90
Number displayed: 1’234’568.00

Number typed into textfield: 1234567890.10
Number displayed : 1’234’568’000.00

Maybe I did not apply the correct formatting or I’m not getting it at all - but from the docs I believe that the format above is not working as expected.

Is there another way (MBS?) to get high numbers formatted into a textfield, not rounded and not in scientific notation?

I notice that we write large numbers differently (1,234,567 = 1’234’567) and I wonder if the format function understands the equivalence of “,” as specified in the format string, and ’ (apostrophe?) as input by the user. I also wonder if the text encoding of the user’s input is what the format function is expecting.

oh, sorry: of course that is no input by the user. That is the OUTPUT of the function. Please excuse me.

I think I have to skip the built in format function and use MBS instead:

To work around the automatic conversion to scientific notation I use this hack (called from GotFocus handler)

[code]Function imGetNumberString(Number As String) As String
’ Clean a formatted number string from apostrophes
’ and return it with 2 digits after decimal point
Dim n As NumberFormatMBS
n=New NumberFormatMBS

Dim exp As Int64 = Number.CDbl * 100
Dim cExp As String = Str(exp)
Dim Eur As String = Left(cExp,Len(cExp)-2)
Dim Cts As String = Right(cExp,2)
Return Eur+n.DecimalPoint+Cts
End Function

i am using textarea instead of textfield. i check again and it seem ok…

What datatype is holding the number value? A Double? then that is your issue… over certain values doubles break down in the decimal area. if you only need TWO decimal digits… use CURRENCY

my datatype is double for those field… does it problem occur on mac or windows??

Is that exactly the format string that you are using in your code?
The right most comma should be a point. Or I am wrong?

And like Dave wrote, I prefer using Currency type when it comes to money values.

oops… sorry… i don’t use format on the textfield, i used code in the lostfocus event for the textarea

Yes, I use doubles, not currency. Doubles should easily be able to hold high values.

From the docs:

[quote]A Double is an intrinsic data type. A Double is a number that can contain a decimal value, i.e., a real number. In other languages, a Double may be referred to as a double precision floating point number. Because Doubles are numbers, you can perform mathematical calculations on them. Doubles use 8 bytes of memory. The default value of a Double is 0.0.
The VarType function returns a value of 5 when passed a Double.


Numerical limits
The maximum value of a double is: ±1.79769313486231570814527423731704357e+308
The minimum value towards zero is: ±4.94065645841246544176568792868221372e-324[/quote]

The format function seems to create the issue. Why is the format function rounding anything above one million?? It means that I can’t use the format function, it just creates punk.

I will try to workaround by multiplying by 100 and store it into a int64, which I will store it in Sqlite into an Integer field type.

From the Sqlite docs:

Then I intend to use the format function to display those values (divided by 100) in a listbox. Here the format seems to work, even for numbers above 1 Million.

To display the correct value when a textfield gets focus, I will use my imGetNumberString function (see above) in the GotFocus event.

Let’s see…

Hmm - just for testing, I changed any double in my code to currency and now everything calculates and displays as expected!

As it says in the docs:

So using currency as datatype seems to be the answer to my issue. My app still has to undergo further tests and investigation.
But thanks for the hint!

No matter what language you are using, when dealing with financial calculations, doubles are about as useful as a chocolate teapot!

Do I change the code or the field on the table?

Both if possible - depending on the database, you need to see how an XOJO currency is mapped to the database type.