Why doesn't my Access query subtracting two number result in zero when it is subtracting the same number from itself

My query is using two tables; linked field is WeekNumber
I am subtracting AdjYTD from FTYTDQuota
those fields are both data type General Number, Double
It subtracts fine except when the numbers are both the same
46.08 - 46.08 = 7.105427357601E-15

Here is the sql:
SELECT qry_WeekNbrYTD.WeekNbr, tbl_quotaAdjustmts.AZID2, qry_WeekNbrYTD.FTYTDQuota, tbl_quotaAdjustmts.AdjYTD, [FTYTDQuota]-[AdjYTD] AS YTDQuota
FROM qry_WeekNbrYTD LEFT JOIN tbl_quotaAdjustmts ON qry_WeekNbrYTD.WeekNbr = tbl_quotaAdjustmts.WeekNbr;
schneider_ksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Because they are singles or doubles. Try this:

CCur([FTYTDQuota])-CCur([AdjYTD])

/gustav
0
Nick67Commented:
Both are Doubles
That's a lot of decimal points, and the truth is, computers aren't that accurate that far out UNLESS you push system to be very precise.

So your result is actually
0.000000000000007105427357601
Which, for your intents and purposes is zero.
So force the issue

SELECT qry_WeekNbrYTD.WeekNbr, tbl_quotaAdjustmts.AZID2, qry_WeekNbrYTD.FTYTDQuota, tbl_quotaAdjustmts.AdjYTD, Round([FTYTDQuota]-[AdjYTD],6) AS YTDQuota
 FROM qry_WeekNbrYTD LEFT JOIN tbl_quotaAdjustmts ON qry_WeekNbrYTD.WeekNbr = tbl_quotaAdjustmts.WeekNbr;

How many decimal places of accuracy do you are about.
I've thrown in 6 for fun.
How many do you care about?
Currency? 2 maybe 4?

The way a binary processor does math isn't what we learned in school.
Ones and zeros, that's it
Integer math.
The minute you want decimal precision, there's emulation on the go
I may not get all the details right.  A CS guy would.
But 4.832 - 6.1 isn't what a computer does.
It does 4832 - 610 in binary and then figures out the decimal placing and hands it back.
With things near an integer, that approximation can sometimes be imperfect
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schneider_ksAuthor Commented:
Thanks so much!  I added the Round and ,2 to my query and it works perfectly.  The other answer specified currency, but those aren't currency fields.
0
Gustav BrockCIOCommented:
> but those aren't currency fields

That doesn't matter. It just rounds to 4 decimals in the simplest way possible.
Amazing you didn't even try. Oh well.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.