CurrencyValue small number creates error

  1. 9 months ago

    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 ?

    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.

  2. Dave S

    18 Mar 2017 San Diego, California USA

    what happens if you do this

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

    SQLite doesn't offically have a currency datatype....

  3. Christian S

    18 Mar 2017 Pre-Release Testers, Xojo Pro Germany

    Currency value only supports 4 digits behind dot.

    If you need more use a double or a BigNumberMBS.

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

  5. Edited 9 months ago

    @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

  6. Jean-Yves P

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

    what do you get as a stringValue ?

  7. just assign the double to a currency variable

         dim currencyValue as Currency = rs.field("aFieldXYZ").DoubleValue
  8. 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.

  9. Christian S

    18 Mar 2017 Pre-Release Testers, Xojo Pro 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.

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

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

  12. Btw thanks alot guys ! very appreciated !

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

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

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

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

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

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

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

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

    It doesn't

  21. Newer ›

or Sign Up to reply!