ACCESS 2003 not recognizing two numbers as equal on a form

I have a form that the users inputs totals, then in another section they input details.  There is a check that the total amount should equal the sum of the details.  Most of the time it work,s but everynow and then (like now!) while the numers look the same, internally they are slighly different, so the VBA will throw an error and the user gets a message box his numbers don't match, when visually they do.  What would be the best approach to tell ACCESS that these numbers really are the same?  Right now I have 67,637.60 in two fields, but internally they have a slight difference.
Sandra SmithRetiredAsked:
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.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
By slightly different I suppose you mean several decimal points out?

You might try something like

If Round(YourNumber1, 2) = Round(YourNamber2, 2) Then
 .....
0
AielloJCommented:
ssmith94015:

It appears you are experiencing classic MS-Access rounding errors.  Many times the math is done in floating point, producing numbers 5 and 6 places out.  Try the ROUND function.  A reference is available at:

http://www.techonthenet.com/access/functions/numeric/round.php

regards,

AielloJ
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:

And highly recommended reading:

http://www.xbeat.net/vbspeed/c_Round.htm

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This might be a better test:

If Left(YourNum1,InStr(1,YourNum1,".")+2) = Left(YourNum2,InStr(1,YourNum2,".")+2) Then

....

This caps it off at 2 dec pts.

mx
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
Sandra SmithRetiredAuthor Commented:
I think you both have the problem nailed.  Will try suggestions and be back.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I'm not sure Round is going to be 100% fool proof.  However, limiting the value to two dec pts should always work.  Round *could* possibly still make the numbers different.

mx
0
Sandra SmithRetiredAuthor Commented:
Mx, would you suggest,then, that

Left(YourNum1,InStr(1,YourNum1,".")+2) = Left(YourNum2,InStr(1,YourNum2,".")+2)

would be a better choice than ROUND?  

I read both source articles, thanks for the references.
0
Sandra SmithRetiredAuthor Commented:
I do have the numbers limited to 2 decimal places and they should not be inputting anything with more than that anyway.
0
Sandra SmithRetiredAuthor Commented:
I went with this version as I am concerned about the chance of ROUND not producing the results I want - otherwise the user is stuck with the form open as there would be no way to close it if there is a difference in any of the compared values.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"would be a better choice than ROUND?  "
That's what I'm thinking.

"and they should not be inputting anything with more than that anyway."
Right ... but this is happened when summing the results, right?  What are examples of these differences?

mx
0
Sandra SmithRetiredAuthor Commented:
yes, they do input more than they should and what I also found out, they are inputting what they think are 0's in some of the fields and no, they are not really 0.  So, while the display may show only two values, those sneaky, pesky little invisilble decimal values are messing up the calculations.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I see.  Although I'm not a big fan of Input Masks, in this case it might make sense ...?

mx
0
Sandra SmithRetiredAuthor Commented:
Just might.  I am going to see how the current change works and may go that route as well if necessary, thanks for the suggestion.
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 Applications

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.