Data Type Calculation error

[MrM121]

I need help big time with this problem. I have a database with about 26 fields on one form, and 25 of these fields need to add up and equal the other field. These fields all have double data types so I can use a decimal. I am only going to 2 decimal places, but the error is this:

I add the 25 fields, using the NZ function as well to get rid of nulls. The problem is, when I do the validation check to see which message to display, the value I get from deducting the single field from the summation is 1.72342334534E-16.

Can anyone help/explain why.

Thanks

Nick

PS I have also tried setting the summation and the single field to a Variant, but the error still occurs.

 

[KenReay]

Hi

The why is because Doubles (and Singles) are binary representations of decimal numbers and the representation is not exact,

Try using CurrencyType instead



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MrM121]

Thanks for that, works just the way I want it to, although I think it's a little daft as sometimes the code works.

On a related note, do you know how to make sure that only 2 numbers can be entered after the decimal point, as the decimal point property is just for showing the value. I can still type a very large number in.

Thanks

 

[KenReay]

Hi

Decimal type may allow this, You can still type n decimal places, but is seems to only store 2 (or whatever you specify), I have not used it so not sure

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KenReay]

Hi

Another way to do it, in the after update event of the control on the form

Private Sub DecPlaces_AfterUpdate()
DecPlaces = Format(DecPlaces, "########0.00&quot
End Sub


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[oharab]

Or use the input mask so they can only enter 2 decimal places!

B

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"
NoFX-The Decline
----------------------------------------------

 

[KenReay]

Hi

Indeed yes, you can use the input mask, although I find users often dislike the way it behaves

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK