FormatCurrency

[SteveMac32]

Hi I am trying to get a variable to hold 7 decimal places, simple i thought use FormatCurrency but this does not seem to work with a variable of currency? Try the code below


Dim UnitPrice As Currency

UnitPrice = FormatCurrency(2.1276595, 7)


UnitPrice will = 2.1277


The Currency variable is rounding on and only allowing 4 decimal places and so returning 2.1277

will I have to define the variable as something else like double to make it work?

Thanks

Steve

 

[JerryKlmns]

SteveMac32

Single or Double hold more digits but the problem of accuracy and precision shows up!

Currency Data Type

Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. The type-declaration character for Currency is the at sign (@).

The Currency data type is useful for calculations involving money and for fixed-point calculations in which accuracy is particularly important.

 

[Golom]

As Jerry has noted, Currency is limited to four digits after the decimal place. If you need precise representation of the numbers then use a decimal data type. If approximate values are sufficient then a Double will do.

Dim UnitPrice As Variant
UnitPrice = CDec(FormatCurrency(CDec(2.1276595), "0.0000000"))

or

Dim UnitPrice As Double
UnitPrice = CDbl(FormatCurrency(CDbl(2.1276595), "0.0000000"))

 

[SteveMac32]

Thanks all I have opted for the Double

thanks

 

[johnwm]

If you want to retain the accuracy of Currency variables yuou can always manually scale them. That way you can get your 7DP still retaining your Integer accuracy (no rounding)

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller