Format a Currency value

General
1

I can’t find a reliable option to format a Currency value to display it properly, maybe someone has a suggestion:

take this code (using MBS to avoid a conversion from Double)

  dim v as variant = CurrencyValueMBS("123456789012345.6789")

now doing:

  dim s1 as string = Format(v.currencyValue, "0.0###")

the result I get is "123456789012345.6719"

while doing:

  dim s2 as string = v.stringValue

I properly get "123456789012345.6789"

now, how to format the value to display?
I can’t just display the stringValue because need to take care of the decimal separator, plus other formatting options.

2

I think your test value is too long. Try it with:

“123456789012.6789”

From what I’ve gathered, Currency converts its value to an integer behind the scenes so your test value gets stored as 1234567890123456789, which exceeds the Int32 limit.

BTW, you can now do:

dim v as variant = Currency.FromText( "12345.6789" )
3

I just checked in 64-bit, btw, and have the same problem. If I’m right about how Currency is being handled internally, it seems like switching from Int32 to Int64 on the backend would solve the issue.

But that’s just speculation. If you really need values that large, you may have to use Double.

4

Kem,
the Currency data type is always handled at 64 bits.
From the docs:

This is a 64-bit fixed-point number format that holds 15 digits to the left of the decimal point and 4 digits to the right.
Numerical Range: -922337203685477.5808 to 922337203685477.5807

So my number is perfectly in range.

The problem is Format() convert it to a Double for displaying and this will show a wrong value.
So my question was an alternative to display the value without passing to Double.

5

If I had over a hundred trillion dollars, I’d hope that I wouldn’t be worried about 7/10 of a cent. :wink:

6

Not dollars unfortunately, but this is a RATE used in multiplication, therefore it must be exact :wink:

7

maybe this could be useful:

[code]Public Function Get_CurrencyAsFormattedString(original_number as currency) as string

dim original_str, var1, def as string
dim entero as Int64
original_str=str(original_number)
entero=original_number

if original_number=entero then
def=str(original_number,"###,###,###,###,###")
def=ReplaceAll(def,",",".")
else
def=str(original_number,"###,###,###,###,###.####")
def=ReplaceAll(def,",",“x”)
def=ReplaceAll(def,".",",")
def=ReplaceAll(def,“x”,".")
end if

if original_number<0 then
def="-"+def
end if

return def

End Function[/code]

8

Sorry to hear that. I was really envious of your clients until you clarified this.

9

if it is a rate, then you should store it as a double and not as a currency ?

10

[quote=300742:@Massimo Valle]the Currency data type is always handled at 64 bits.
From the docs:[/quote]

Then I’d say you’ve uncovered a bug. The only question is, is it a bug in the code or the documentation?

BTW, if you store that currency in a double you get 123456789012345.671875, which seems to be the heart of the problem.

11

I believe the documentation is correct and currency always had problems. :frowning:
And most of them comes passing from a Double when “handling” it

12

[quote=300746:@nicolscanessa]maybe this could be useful:

[code]Public Function Get_CurrencyAsFormattedString(original_number as currency) as string

dim original_str, var1, def as string
dim entero as Int64
original_str=str(original_number)
entero=original_number

if original_number=entero then
def=str(original_number,"###,###,###,###,###")
def=ReplaceAll(def,",",".")
else
def=str(original_number,"###,###,###,###,###.####")
def=ReplaceAll(def,",",“x”)
def=ReplaceAll(def,".",",")
def=ReplaceAll(def,“x”,".")
end if

if original_number<0 then
def="-"+def
end if

return def

End Function[/code][/quote]

Thanks Nicols, will try your approach :smiley:

13

The format function converts every number first into a double and there origins the problem that large int64 and currency numbers can not be larger than the double can represent and will therefor be inaccurate.