755 views

# CurrencyValue small number creates error

1. ‹ Older
2. last year

### Etienne P

18 Mar 2017

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

3. ### Etienne P

18 Mar 2017
Edited last year

@Dave S what happens if you do this

`Currency(rs.field("aFieldXYZ").doubleValue)`

Gave me an error...
«Expected a currency, but got a Double»

`Return Currency(pRecordSet.IdxField(pIndex).DoubleValue)`

and my return type is Variant

4. ### Jean-Yves P

18 Mar 2017 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

what do you get as a stringValue ?

5. ### Norman P

18 Mar 2017 Xojo Inc https://guykawasaki.com/the_ar...

just assign the double to a currency variable

`     dim currencyValue as Currency = rs.field("aFieldXYZ").DoubleValue`
6. ### Jeff T

18 Mar 2017 Midlands of England, Europe

Doesnt 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.

7. ### Christian S

18 Mar 2017 Pre-Release Testers, Xojo Pro, XDC Speakers Answer Germany

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.

8. ### Dave S

18 Mar 2017 San Diego, California USA

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.

9. ### Etienne P

18 Mar 2017

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... :-/

10. ### Etienne P

18 Mar 2017

Btw thanks alot guys ! very appreciated !

11. ### Dave S

18 Mar 2017 San Diego, California USA

@Etienne P 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... :-/

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....

12. ### Norman P

18 Mar 2017 Xojo Inc https://guykawasaki.com/the_ar...

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

13. ### Etienne P

19 Mar 2017

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...

14. ### Dave S

19 Mar 2017 San Diego, California USA

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

15. ### Norman P

19 Mar 2017 Xojo Inc https://guykawasaki.com/the_ar...

@Etienne P 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 :-/

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

16. ### Etienne P

20 Mar 2017

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 :-)

17. ### Jean-Yves P

20 Mar 2017 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

do you make use of the round(x,y) function in your sqlite calculations ?
it should help to round the result to 4 digits.

18. ### Norman P

20 Mar 2017 Xojo Inc https://guykawasaki.com/the_ar...

@Etienne P Norman : i dont know.. it look like SQLite treat everything like a String

It doesn't

19. ### Etienne P

20 Mar 2017
Edited last year

Jean-Yves : yes it works, i rarely use it because i lost precision in taxes calculation, but the last calculation in the query i should use it to avoid that strange number :-)

20. ### Etienne P

20 Mar 2017

Norman : it return a number if you ask a number, but you can insert anything in a real / integer column. MySQL and PostGres you can't insert a string in a number column

21. ### Norman P

20 Mar 2017 Xojo Inc https://guykawasaki.com/the_ar...

Sure but that doesn't mean it uses strings
Just that it does not enforce typing in the same way postgres etc do