I have created a TABLE with a Currency COLUMN, but …
LB.CellTextAt(LB.LastAddedRowIndex,4) = row.Column("SRU").CurrencyValue.ToString
Have-you used the Currency symbol ?
I lost the cents…
So I will use .StringValue as usual unless someone have a better idea ?
I’m not sure this qualifies as a better idea Emile but it works for me 
. When I create the SQLite table, I store currency values as the SQLite column type REAL. Then I retrieve them using .CurrencyValue as you are doing.
I stored them as Currency. And they are stored correctly in the .sqlite file.
Any other taker ?
Can you check the values on your database?
Are the values saved as 123,45?
Could it be that having , instead of . is causing problems?
Yes Alberto, I checked the values and they are correct. Worst, I used StringValue and get the same result. I think I’m goin’ to send this project to bed and come back to it tomorrow. (after a reboot).
Can you explain what ‘correct’ means?
You see 123,45 or do you see 123.45 on the database?
Usually when people don’t get the cents is because Xojo sees a comma instead a period and drops the rest. Note: this is just a guess.
I see exactly what I stored. Now I think my brain is full.
In the .sqlite file I have 123,456 that I saved… as string… in a Currency Column.
Yes, I am tired. I have to explore a little bit after some sleeping. Hey, it’s nearly mid-night here.
Data stored in a database should always use “.” as the decimal separator. Store data according to the standards. Then display it according to the locale.
123,456 in a currency column is invalid data.
That’s the problem.
Edit: see Tim’s comment.
That’s what I was trying to say. If you have currency with comma as decimal separator you will get this problem.
I had doubts, so I used System.DebugLog (in addition to the DesktopListBox display). And you know what ?
In the DesktopListBox, the display is wrong (stop at the decimal separator)
with SYstem.DebugLog: all is correct ! With the correct monney symbol !
Without reboot. So, I Powered off the computer and here I am ready to continue the quest (if I may say so).
I found the faultive:
LB.ColumnAlignmentAt(2) = DesktopListBox.Alignments.Decimal
Once commented, I get my prices with the decimal separator, the decimal parts and local currency character as I set in Locale…
Sometimes good is the foe of best (or something like that…).
A bug ?
Probably. I checked with Center instead of Decimal and I get what I asked on the DesktopListBox.
It works too with Right Align…
It’s a bug: if I add
LB.ColumnAlignmentOffsetAt(2) = -30
The data are correctly displayed (Decimal Aligned).
That’s correct because you have not used the offset. 
How should your app align decimal if it doesn’t know how many decimal places you want to display?
So you always have to specify an offset. Otherwise, decimal will be aligned up to the decimal point… 
A simple § in the documentation in the AlignDecimal location would avoid me to waste so may hours…
But the offset works also if you align Right, not only Decimal. Your explanation looks… suspicious 
So bug or not bug ?
Since the offset might not only be of interest in decimal alignment, the offset can of course be applied regardless of the alignment.
No bug. 
A bug. In your code. In the way you store the data in the database.
Can you expand your thinking, please ? I do not get it.
You like to refer to literature in documentation, forums and the like. However, when you need an answer yourself, you often like to take the easy way out… 
https://sqlite.org/datatype3.html
@Jason_Tait already recommended to use REAL, but you just dismissed it…
Thisa topic was discussed at length some time ago on the SQLite Users Forum. My possibly faulty recollection is that there wa a suggestion to use INTEGER, but multiply values by 10000, and do the appropriate division later to restore pounds and pence. The extra two decimal places then cover rounding errors.
@ Sasha:
a. I come to the forum after making extensive searches.
b. There is a Currency Field Type, why will I search work-arounds (REAL)…
Currency:
A decimal number that holds 15 digits to the left of the decimal point and 4 digits to the right.
I am only trying to do the things how they have to be done.
Single entry in a ListBox:
I do not add the Euro (€) symbol. Xojo (via Currency) do it for me.
@Tim: thank you for this explanation, but the goal is reached, and I do not asked about that.
All: Sorting on the Currency Columns works fine (apparently) on the small amount of Records I have for testing (11).
Regards
You haven’t provided any code. I’m happy that your efforts give you the result you needed.
Yes, currencty.ToString with a locale will give you the money (Euro in this case) symbol, but that is not a problem related with your database.
You are confused and may confuse other people. SQLite uses REAL, you can define as Currency if you want. And you can even save invalid data to it like “money123”.
See example:
EmileCurrency.xojo_binary_project.zip (16.0 KB)
The table is created with this sql:
Var sql As String = "CREATE TABLE Team (ID INTEGER NOT NULL, Name TEXT, Coach TEXT, City CURRENCY, PRIMARY KEY(ID));"
Here is the information saved to the ‘CURRENCY’ field:
My guess (again, as you didn’t provide any code) is that you are saving Strings (as you said before) like 3,45 and to fix your currency problem you are reading now as String and converting that to currency, then using Currency ToString to get the Euro symbol without you adding it.
I hope this helps.
That’s in the Xojo realm. Native Xojo type “Currency” behind the scenes is just an Int64 with a fixed decimal of 4 digits, that means that the value 12.34 is stored internally as an integer 123400 and converted by Xojo to a double as (Currency = int64Value / 10000) for intermediate calculations.
But as Alberto said in the SQLite realm, the primitive types are simple as TEXT, INTEGER, REAL and BLOB and depending on how you make your code trying to put a “currency” there your values can end in the DB as REAL or TEXT. SQLite stores values as Javascript or PHP or Python… it does not care, it decides the type on the fly depending on the source value.
So a “currency” field in SQLite can receive a string “12,34” or a float 12.34
And it does not compute correctly with incorrect non-standard values as “12,34” discarding decimals as it only accepts point for a dynamic conversion to float.
There are 2 layers that confuse people, the backend storage layer and the frontend presentation layer. Because due to locale you may see “12,34” it does not mean that it is stored as that.
