Table fields adding incorrect. 1.0000001192 instead of 1 1

[Poduska]

I have a table with 7 fields at the end, one for each day, single 2 decimals. The values are .4,.2,.1,.1,.1,.05,.05 which is 1, well at least when I was in school. but when I could not get my data validation to work I ran a query and added them all together and get 1.00000011920929

What gives?

 

[RoyVidar]

This is called floating point calculations, where datatypes single and double aren't precise. There's a geeky explanation here

http://support.microsoft.com/kb/42980/en-us,

but if you need precision, take a look at the Currency datatype.

Roy-Vidar

 

[Poduska]

Is this because I am using .4 or decimal values? If I key in the same data into Excel, sum it up and ask if the sum is = to 1 I get TRUE.

I have never experienced this before but how are you expected to EVER compare two numbers if you will always get weird decimals when you do addition?

Just frustrated that such simple math cannot be solved in access.

 

[RoyVidar]

Don't compare values/variables of datatype single or double with equal sign. For that use integers, currency or decimal. The latter two might be called scaled integers.

For more information about them, hit F1 while the cursor is within the datatype column in table design view (Currency), for the others, place the cursor in the Field Size row in the field properties, and hit F1 again.

As there is no equivalent VB(A) datatype to Decimal (one would need to use the Decimal subtype of Variant), most prefer using the Datatype Currency when precision and decimals are needed.

Roy-Vidar

 

[Poduska]

While the finer details of why I have to use Currency escape me, and will have to wait for another time to do the research, it does appear to do exactly what I want.

I have to admit I probably would never have tried currency but if the teacher from Tek Tips says.... then I do!

Star for the help. I can stop pulling my hair out.

 

[RoyVidar]

Thanx,
it is perhaps a bit hard to both explain and understand, here's another link with some Microsoft information

http://msdn2.microsoft.com/en-us/library/aa189284(office.10).aspx,

then links to "The Floating-Point Data Types", "The Currency and Decimal Data Types" and "Conversion, Rounding, and Truncation" can perhaps be of assistance? Or [google]floating point errors[/google], but that'd constitute weeks of reading ;-)

Roy-Vidar