How to have a total by summarizing the face value for each line in invoice query?

1. Novice Windows 10 Access 2016  Join Date
Jan 2019
Posts
1

How to have a total by summarizing the face value for each line in invoice query?

How to have a "correct" total by summarizing the value for each line in invoice query?

Example:
line 1 subtotal = \$123.51 (but actual value is 123.514)
line 2 subtotal = \$123.43 (but actual value is 123.434)
line 3 subtotal = \$123.88 (but actual value is 123.884)
line 4 subtotal = \$123.99 (but actual value is 123.994)
line 5 subtotal = \$123.77 (but actual value is 123.774)

Because of decimal value in currency mode, we will get subtotal as above mentioned, eg \$123.51. If we set a SUM in report footer, we will get \$618.60 in total.
But, in paper printing, we will think suppose to have the total \$618.58 . There is difference \$0.02 .

I tried some steps like put it as Fixed / General Number , or even change decimal , but could not solve this.  Reply With Quote

2. Virtually Inert Person Windows 10 Access 2016           Join Date
Jun 2014
Location
Posts
5,756
You tried those fixes where?
If your table field is single or double data type set to 3 places and your query is a Totals query using Sum, and that field is set to Currency (query property sheet) and the decimal count is set to 3 in the query, I would think it ought to work. Warning - if you switch to single or double in the table from some other type, you might introduce floating point errors so only play with a copy of your table.

If you find that works, you can always update your "fixed" table (assuming you've altered the number type) using your original table via update query but I would even perform the update on a test copy first.  Reply With Quote

currency, decimal, sum 