Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Unanswered: SQL Calculation Problem



    I am performing a calculation in both SQL and MSACCESS. ACCESS is correct and SQL is not. It is off by -.05 Here is the calculation:

    Sum((ch.FaceAmount)-(((ch.FaceAmount*Ex.CAN_Exchange_Rate+0.005)*100)/100)) AS TransAmount,


    Both FaceAmount and CAN_Exchange_Rate are defined as money in a SQL table. The ACCESS front-end attaches this table, so it uses the same columns in the calculation.

    Now, I know SQL looks at money with 4 decimal places. The attached SQL tables in ACCESS looks at these columns as currency which I believe is 2 decimal places. I think this is where the descrepency is. But it still doesn't explain why SQL is calculating the incorrect total.


    Does anyone have any ideas?
    Gail

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Access actually stores more than 2 decimal places (I think 4...) for calculation purposes, but doesn't display fractions of a cent.

    I think the problem may be caused by your inclusion of Integer values in your formula. Try this instead:

    Sum((ch.FaceAmount)-(((ch.FaceAmount*Ex.CAN_Exchange_Rate+0.005)*100.0 00)/100.000)) AS TransAmount,
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2004
    Posts
    2

    Unhappy SQLCalc Problem

    I tried the suggested calc:

    Sum((ch.FaceAmount)-(((ch.FaceAmount*Ex.CAN_Exchange_Rate+0.005)*100.0 00)/100.000)) AS TransAmount,


    and it didn't work. Any other suggestions?


    I appreciate the help,

    Gail

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Please supply some paramter values for:
    ch.FaceAmount
    Ex.CAN_Exchange_Rate
    ...and the result you are getting.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •