ms access: simple subtraction, wrong answer

Question

181.72 - 181.00 = 0.719999999999999

Why does Access do this? How can I get the correct answer of 0.72

Use `CDec(181.72) - 181` but read the answers in the linked question to understand why. — 41686d6564 stands w. Palestine, Oct 28 '19 at 04:21
thanks @AhmedAbdelhameed, this gets the right output but unfortunately this CDec() doesn't work in queries or reports where I need to use it. I could use a custom VBA function but would prefer to avoid it if possible. — rakewell, Oct 28 '19 at 04:37
You probably should have specified where you needed to use it in your question then. It makes a difference. — braX, Oct 28 '19 at 04:39
Have you tried just formatting it in the report after it's calculated? — braX, Oct 28 '19 at 05:01

Gustav · Accepted Answer · 2019-10-28T07:14:02.180

Always use Currency as data type (also for your table fields) for amounts and quantities that require no more than four decimals:

Result = CCur(181.72) - CCur(181.00)
Result -> 0.72

For more decimals, use Decimal:

Result = CDec(181.59898) - CDec(181.00)
Result -> 0.59898

For use in a query, CDec will fail, so write a function to call it:

Public Function CVDec(ByVal Value As Variant) As Variant

    Dim Result As Variant

    Result = CDec(Value)

    CVDec = Result

End Function

1 Answers1