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 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.
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
[/code]
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
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.
Notes
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.
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!