Hi guys !
i got a little problem, one of my SQLite query (calculation blabla) return a very small number : -2.90967250293761e-12
when i use rs.field(“aFieldXYZ”).currencyValue, i got this :
-2.9096
expected :
0.0000
any idea how to fix this ?
what happens if you do this
Currency(rs.field("aFieldXYZ").doubleValue)SQLite doesn’t offically have a currency datatype…
Currency value only supports 4 digits behind dot.
If you need more use a double or a BigNumberMBS.
i only need the 4 digits… this why i was using the currency value (and less trouble for accounting calculation) … so i really expect 0.0000 … not a -0.00000000000290
[quote=321398:@Dave S]what happens if you do this
Currency(rs.field("aFieldXYZ").doubleValue)[/quote]
Gave me an error…
«Expected a currency, but got a Double»
[code]Return Currency(pRecordSet.IdxField(pIndex).DoubleValue)[/code]
and my return type is Variant
what do you get as a stringValue ?
just assign the double to a currency variable
dim currencyValue as Currency = rs.field("aFieldXYZ").DoubleValueDoesnt SQLite store everything as a string, though?
What does the field ACTUALLY contain?
Is it a string holding ’ -2.90967250293761e-12’
If so, its the code that stores the value which has the issue.
You could / should tidy up the value before storing it.
Multiply by 10000, take the integer, then divide by 10000 and store the result. Maybe using format as a last resort.
double is not perfect.
you could add some code like this:
if v > 0 and v < 0.0001 then
v = 0
elseif v < 0 and v >= -0.0001 then
v = 0
end if
This way you round near zeros.
While double is not perfect… it should be “perfect” enough for the OP needs… since Double will be accurate to way more than the 4 decimal points he needs…
My quesition is where is the data coming from in the first place?
ie. how did that value end up in a “currency” field in the database?
perhaps the solution should be on the storage side, not the retrieval side.
Dave : from a calculation made in a SQLite query… strangely i substract a select from and other select, both numbers are 2 or 3 digits only after the dot… but it gives me a stupid number like X.XE-12 instead of zero
Also i needed to deal with Xojo sqlite plugin that treats this number as an int64, switch it to double an then do the tricks as Christian said… :-/
Btw thanks alot guys ! very appreciated !
[quote=321429:@Etienne Pilon]Dave : from a calculation made in a SQLite query… strangely i substract a select from and other select, both numbers are 2 or 3 digits only after the dot… but it gives me a stupid number like X.XE-12 instead of zero
Also i needed to deal with Xojo sqlite plugin that treats this number as an int64, switch it to double an then do the tricks as Christian said… :-/[/quote]
then it is not surprising you got the results you did… as Christian said “doubles” are not perfect, and for all intents, SQLite is kinda “typeless”… so it converted the arguments to “double”, did your calculation and returned the double result.
As I stated, Sqlite itself doesn’t know what “currency” is, this is an Xojo addition to their interface…
My suggestion would be to alter the query you mentioned so it fixes the result before storing it…
SQLite will do the computation and give a result that is normally a double
Assign the recordset doublevalue to a currency value and life will be good
dim currencyValue as Currency = rs.field("aFieldXYZ").DoubleValue
done
Norman : my ORM check all the kind of data that came from the DB (sqlite or mysql) and in that case, it’s a «19» don’t know why, but it gives me some headache :-/
Dave : the funny thing it’s a select minus an other select. Both, for exemple, respectively return 8.12 and 8.12 … normally it’s supposed to give 0 ? … in my case it’s a x.xxxxE-12 dont know why ! AND the query for each is really simple, they sum(fields) with 4 digits only in the db…
anyway, i had to circle aground it…
I stand behind my suggestion…
each of your SELECT is coercing a value into a DOUBLE to perform the subtraction… the result of that subtraction is the problem
[quote=321571:@Etienne Pilon]Norman : my ORM check all the kind of data that came from the DB (sqlite or mysql) and in that case, it’s a «19» don’t know why, but it gives me some headache :-/
[/quote]
Guess it depends on how you put the data in
A currency IS , when all is said & done, an int64 with special interpretation so it has 4 decimals
Dave : Your’re right ! all my software are build on what you said… but in that case, SQLite took some drugs…
Norman : i dont know… it look like SQLite treat everything like a String… anyway, i figured something 
do you make use of the round(x,y) function in your sqlite calculations ?
it should help to round the result to 4 digits.
[quote=321746:@Etienne Pilon]
Norman : i dont know… it look like SQLite treat everything like a String[/quote]
It doesn’t