CurrencyValue small number creates error

  1. ‹ Older
  2. 7 months ago

    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. Edited 7 months ago by Etienne P

    @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. 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 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. 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. 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. 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. 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. @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. 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. @Etienne P Norman : i dont know.. it look like SQLite treat everything like a String

    It doesn't

  19. Edited 7 months ago by Etienne P

    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. 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. Sure but that doesn't mean it uses strings
    Just that it does not enforce typing in the same way postgres etc do

or Sign Up to reply!