How I can format a currency value to display on screen?
The Format() method unfortunately handle Doubles and this lead to rounding errors with big quantities.
Example:
dim v as variant = "999999999999.9999"
dim s as string = Format(v.CurrencyValue, "0.0000")
[quote]dim v as currency = 999999999999.9999
msgbox Format(v, “0.0000”)
while even this DOES NOT … which REALLY surprised me[/quote]
I remember reading somewhere that currency is internally treated as a int64 data type, multiplied by 1000. dim’ing v as currency = 999999999999.9999 probably rounds it up to the nearest integer.
to test my theory, I tried:
dim v as currency = 999999999999.999 ' notice just 3 decimals
msgbox Format(v, "0.0000")
dim c as Currency = 999999999999.9999
dim mantis as currency = c-floor(c)
system.debuglog format(floor(c),"###") // 99999999999
system.debuglog format(mantis,"####") // 9998
system.DebugLog c.totext // 999999999999.9998
It looks as if some rounding happens within c itself the other way.
With 10 digits to the left the result is right. But Currency is indubitably broken. According to the LR :
With 15 nines to the left, the result becomes completely incoherent. Totext gives -0.5808 !
The result is the same using Xojo.Core.
Now, UInt64 gives a much better result :
dim uu as UInt64 = 9999999999999999999
system.DebugLog uu.totext // 9999999999999999999
system.DebugLog uu.totext.Left(15)+"."+uu.totext.right(4) // 999999999999999.9999
If I had the choice between an unreliable car and a van that runs, I would not hesitate much.
From what I see, your report from September 2014 has a status of Fixed and Verified. Since the last update stating that status is from June 4, 2015, the current state of madness of the type may indeed be fixed in 2015R3.
Now I am not currently needing a stable and reliable type for accounting, but if I were, for the time being and pending the release of 2015R3, plus a thorough verification, I would not touch currency.
Problem is I need currency how is implemented (including the support in Variant), and should not be so difficult to fix it if we can build a substitute using UInt64.
I just re read <https://xojo.com/issue/35189> very carefully. It lists two different issues. One is the rounding error, the other is the possibility to assign a numeric value directly.
I do not know which one has been fixed or both.
More terrible, after the test I conducted with series of nine, I started to wonder if the test in that report would render different results if instead of 123456789012345.1234 I used 999999999999999.1234.
[code] dim value as string = “999999999999999.1234”
dim c1 as currency = val(value)
dim c2 as currency = ctype(val(value), Currency)
dim v as variant = value
dim c3 as currency = v.currencyValue
break[/code]
I do hope Xojo verified very thoroughly the fix. For the time being, C3 gives 922337203685477.5807 … That is unusable.
In the above you talk about 14 digits and you seem to be only counting those to the left of the decimal point. In fact you have 18 digits since 4 of them are to the right of the decimal. If I remember correctly 32 bit floating point can only handle a max of 15 digits within its numbers. That, by itself helps to lead to your errors. But, as you say, currency seems to have some severe problems when numbers reach a certain value and you attempt to display them.
Fortunately the little budgeting program that I wrote for myself that, uses Currency values, will never encounter those problems since I won’t live long enough to accumulated wealth to those number of digits.
Currency values are 64 bit values
But there are only 63 bits available for the “value” since one is the sign bit
This means the max is somewhere about 922337203685477.5807
So 15 9’s followed by 4 fractional digits literally overflows the currency max value & like all signed values a positive on wraps around to a negative one
[quote=196477:@Norman Palardy]Currency values are 64 bit values
But there are only 63 bits available for the “value” since one is the sign bit
This means the max is somewhere about 922337203685477.5807
So 15 9’s followed by 4 fractional digits literally overflows the currency max value & like all signed values a positive on wraps around to a negative one
*I’m not sure what the min negative is[/quote]
Indeed it looks very much like an overflow. Problem is the LR states very clearly :
Is it not time to correct the LR to indicate the actual practical limit of the current Currency type ?
nine hundred twenty-two trillion, three hundred thirty-seven billion, two hundred three million, six hundred eighty-five thousand, four hundred seventy-seven dollars and fifty-eight cents
[quote=196483:@Michel Bujardet]Indeed it looks very much like an overflow. Problem is the LR states very clearly :
[/quote]
It should state a valid range as well like most of the other numeric data types do
Documentation error
And when you do conversions to other currencies? If you are converting from Euros to something like Vietnamese currency it becomes 37,748,105,251 Euros to overflow.
Personally on that scale I would probably feel safer using a bug number library instead.