When I filter for 0.33 -or- 0.83 I get two decimal places

Round the amounts before you sum them, e.g. if we use the old VAT rate of 17.5%: SUM(ROUND(NetPrice*1.175,2))

I don't see VAT and NetPrice in Nathan's example. Instead, I see a sum of HrsActual, which is composed of instances of 0.33 and 0.83.

I don't know if Nathan's HrsActual are calculated. But using Excel, I was able to duplicate his results with the sum of a column of constant 0.33s (41 instances) and 0.83s (24 instances) in random order. (0.33*41 + 0.83*24 = 33.45)

So, I wonder if the correct suggestion is:

ROUND(SUM(HrsActual), 2)

But again, that should not be necessary if HrsActual is type Currency, as Gustav suggests.

"I don't think you would find any experienced Access developer incorporating the total line at the bottom of a datasheet in an application. In fact it is generally considered that a table's raw datasheet should never be exposed to users, and the interface be only by means of forms and reports."

Plus 1 for the most part.

On the other hand, an experienced Access developer should understand the issues behind such "features" and be in a better position to handle them appropriately. So, the fact that most experienced developers prefer not to use them, at least in most cases, should also tell you something.

Change the data type of field HrsActual to Currency, and you won't see this issue anymore.

I do not know of a way to apply 'Round' to the sum at the bottom of an Access column.

The place to do the summation in Access is in an aggregating query, by calling the VBA DSum function, or by means of a computed control in a footer section in a form or report. In all of those the rounded values can be summed where necessary. Access nowadays includes a number of features which at first sight might seem useful, but in reality are either pointless or dangerous. I don't think you would find any experienced Access developer incorporating the total line at the bottom of a datasheet in an application. In fact it is generally considered that a table's raw datasheet should never be exposed to users, and the interface be only by means of forms and reports.

I would make one caveat with regard to rounding before summing values. We from time to time receive posts asking why totals in an invoice for instance do not exactly match the sum of the currency values in each invoice line. This is because the currency data type in Access is to a precision of four decimal places in order to suppress rounding errors. If, for instance, the VAT or some other sales tax is computed for each invoice line and expressed as a currency value, the sum of the tax payable on the invoice might not match the sum of the amounts you see in each line. This is because the underlying values to a precision of four decimal places are being summed. Whether this is the correct suppression of a rounding error or not differs in different contexts. In my InvoicePDF demo I compute the total VAT payable on the sum of the computed values, i.e any rounding error is suppressed. In other contexts the business requirement might be to sum the currency values rounded to two decimal places. There is no absolute rule as to which is correct, so judgement is necessary.

Nathan wrote:

This is: Microsoft® Access® for Microsoft 365 MSO (Version 2202 Build 16.0.14931.20652) 64-bit

How is this possible? I do not know of a way to apply 'Round' to the sum at the bottom of an Access column.

I do not know anything about MS Access. But I would be surprised if there is not some way to cause Access to round the sum (any calculation), because this is a very common problem.

I will post a request to the moderators to move just your inquiry and my response to that sub-category.

If you do not see that happen within 48 hours, I suggest that you repost just this part of your inquiry to the Access sub-category of this forum.

Is there a fault with the Intel processor (ala the old x86 math coprocessors that had an error in their internal tables.) or is there a bug in the Access program?

Neither. As I explained earlier in this thread (*), this is a natural and common side-effect of choosing to use 64-bit binary floating-point (type Double) internally to represent decimal values.

To reiterate, the problem arises because:

1. Most decimal fraction cannot be represented exactly. They must be approximated.
2. The binary approximation of a particular decimal fraction might vary depending on the magnitude of the value. That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!).

I intend to post more details in response to Guitar96's latest follow-up (*). Take a look at that after I post it.

(*) Referring to the original thread (click here). See all of my related responses there.

When I filter for 0.33 -or- 0.83 I get two decimal places. When I filter for both 0.33 -AND- 0.83, I get a sum of 33.4499999999999 (consistently, it's not randomly alternating with 33.45 (the correct sum)).

Yes, it does "alternate".

Looks can be deceiving if Access formats only up to 15 significant digits, as Excel does.

The sum of 41 instances of 0.33 might look like 13.5300000000000, but in fact there is a hidden residual of about 2.84E-14, an infinitesimal number. (Read: 2.84 times 10 to the -14 power, or 1 divided by 2.84 times 10 to the 14 power.)

Likewise, the sum of 24 instances of 0.83 might look like 19.9200000000000, but there is a hidden residual of about 2.84E-14 (by coindence).

As for the combined list of 0.33s and 0.83s that sum to 33.45, the result depends on their order.

In 40,000 random orders, I found what appears to be 33.4500000000000 about 5.9% of the time. The remainder appear to be 33.4499999999999.

But in fact, none (so far) is exactly 33.45.

What appears to be 33.4500000000000 has a hidden residual of about -4.97E-14 or -3.55E-14 (once!).

And what appears to be 33.4499999999999 has a hidden residual of about 2.84E-14, 3.55E-14 or 4.26E-14.