Ho, Ho, Ho! (to the 4th decimal point!)
Seeing as you are heading into your busy season Gary I’d think any time around the 25th Dec would not be bad 
So has anyone tested the currency type in 2014r3 yet? Did I get a bit of an early Christmas present?
I haven’t tested it myself, but I saw in the release notes that it received some major love. I suspect an early gift. 
Excellent, Bob. I understand storing it as 4 decimal places, but what’s the best rounding practice for display?
I am not a Currency expert. I would suspect that Format would give you the best result but I’m not sure how rounding affects that. Sounds like it make a great article or training video, no?
If there are non-zero values to display then you might want to consider displaying them, otherwise the users will have no way to validate calculations performed by the application since what they see is different than what is stored and used by the application. When it comes to currency values, what they see and what they get should be the same.
I’ve been storing currency as a string in my db to make everything compatible with SQLite. In doing so, I’ve come up with the following function:
Function strToCurrencyString(Extends pStr As String) As String
// We must put the decimal in twice.
// Once for the rounding of the last 2 places, and the other for the actual decimal point
Const decPlace = 2
Dim lStr As Integer = pStr.len
Dim strNew As String = pStr
strNew = strNew.Left(lStr - decPlace) +"." + strNew.Right(decPlace)
Dim d As Double = strNew.Val
d = Round(d)
strNew = Format(d, "#0.")
// remove the decimal
strNew = strNew.ReplaceAll(".", "")
strNew = strNew.Left(strNew.len - decPlace) +"." + strNew.Right(decPlace)
If strNew = ".0" Then strNew = "0.00"
Return strNew
End Function
I also have one that extends a Field rather than a string. Seems convoluted to me. Any suggestions?
The best way to store currency in SQLite is use integer in SQLite and Int64 in Xojo. All integers in SQLite are 64 bit anyway and currency is really Int64 behind the scenes.
Watch out for decimal points! In some countries we use a comma …
Norm posted once some code, at least I think I copied this from him:
[code]Function imGetDecimalPoint() As String
dim d as double
dim s as string
d = 1.1
s = format(d,“0.0”)
// remove all the numbers
s = replaceall(s,“1”,"")
// whats left is the decimal separator
// again it could be multiple characters
Return s
End Function
[/code]
And:
[code]Function imGetThousandsSeparator() As String
dim d as double = 1111.1
dim s as string = format(d, “0,000”)
// remove all the numbers
s = replaceall(s,“1”,"")
// whats left is the thousands separator
// and YOU CANNOT assume it is 1 char as on some systems you can customize it
// and make it anything including multiple characters
Return s
End Function
[/code]
Use Int64 in Xojo and Integer in SQLite. Perhaps the fixes to Currency in Xojo 2014r3 will make it suitable so you don’t have to manage decimal places on your own.
They can be but only when needed. From the SQLite docs:
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
Well, if you’re saving a currency value from Xojo it will be an Int64, no?
No, it should be Real (64 bit floating), able to 15 digits floating precision like: 99,999,999,999.9999
I don’t think so. My understanding is that SQLite Integer will scale based on the value passed. That said, I’ve not examined SQLite databases with a hex editor after loading them with Integers of varying values.
I believe we live in times needing 128 bit floating point numbers for processing and storage. IEEE 754 binary128.
Perhaps it is time to examine some SQLite databases with Xojo Currency values to find out precisely how those values are being stored.
Currency uses floating point with 4 digits precision, that said, in a conversion it will need to be stored in the most equivalent DB format, and in a SQLite, it’s Real. For example, you can’t store a $11,899.77 currency value directly in a int64 due to the 0.77 cents. In a Real, it’s ok.
My understanding is that Xojo uses Int64 for Currency values and handles the four digit precision behind the scenes.
It’s possible, or some packed bcd format.