gpdixit
asked on
MS Access 2003 - sum is producing too many decimal places
SELECT tblDrafts.D_CaseNumber, Sum(tblDrafts.D_Amount) AS SumOfD_Amount
FROM tblDrafts
GROUP BY tblDrafts.D_CaseNumber;
produces sum with many decimal digits instead of two digits as defined in the table. By using Round function, it is not giving correct sum in decimal values. Example, the value in table is say 169531.10 and it is showing 169531.09 because it is rounding 169531.09354...
FROM tblDrafts
GROUP BY tblDrafts.D_CaseNumber;
produces sum with many decimal digits instead of two digits as defined in the table. By using Round function, it is not giving correct sum in decimal values. Example, the value in table is say 169531.10 and it is showing 169531.09 because it is rounding 169531.09354...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Always - always - use data type Currency for currency, amounts, etc.
If you can 't change the fields of the table, convert to Currency at the earliest stage:
The reason for your rounding errors is caused by adding positive and negative amounts when these are Single or Double.
/gustav
If you can 't change the fields of the table, convert to Currency at the earliest stage:
SELECT
tblDrafts.D_CaseNumber,
Sum(CCur(tblDrafts.D_Amount)) AS SumOfD_Amount
FROM
tblDrafts
GROUP BY
tblDrafts.D_CaseNumber;The reason for your rounding errors is caused by adding positive and negative amounts when these are Single or Double.
/gustav
I was wondering when your "Spidey sense" would start tingling, gustav :)
Believe me, it's operating at random!
/gustav
/gustav
ASKER
Expert gave two solutions, out of which second solution was acceptable. I tried and and it is successful. Solution was provided in a very short time. Lot of thanks.
SELECT tblDrafts.D_CaseNumber, formatnumber(Sum(tblDrafts
FROM tblDrafts
GROUP BY tblDrafts.D_CaseNumber;