Problem with summing up values

[hansu]

In an access-program to manage the stock of products I retrieve the following fields from a table to get the remaining stock of a product:
QUANTITY, LENGTH. (And of course the primary key)

If for a certain LENGTH the same quantity has been booked out (negative values) as has been booked in (positive values) the expression Sum([QUANTITY]*[LENGTH]) should return 0.
Instead I receive often a small value like -1.11022E-16.
Any idea to solve this problem?

Datatypes are: QUANTITY = Long, LENGTH = Double.

Thanks for your assistance.

 

[scriverb]

Sum([Quantity]*[Length]) will give you the total linear Feet? of all products that have been selected. So, this equation is very confusing as it relates to the posting of your problem. Why don't you just post your queries SQL so that we may take a look at it for you. The fields you mentioned about booked in and booked out don't seem to be present here so we really can't help until we have all the facts.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hansu]

Thanks for your reply scriverb.
The query is:
SELECT MUTATIONS.PAKID, Sum([QUANTITY]*[LENGTH]) AS Total
FROM MUTATIONS
GROUP BY MUTATIONS.PAKID

This gives the total linear feet for every selected PAKID.
When the user books in a product he adds a record to the table MUTATION with a positive quantity resp. when he books out with a negative quantity.

 

[scriverb]

Do you receive this value for all expected zero values? My suggestion is to identify the PAKID that is displaying this unwanted value. Then display the individual records for this PAKID and see if there is some QUANTITY or LENGTH values that seem abnormal.
Select * from MUTATIONS as A WHERE A.PAKID = [Enter PAKID];

It has to be one of these data entries that has some erroneous data to cause this to happen. Null values or something.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[hansu]

Hi Bob

From more than 6000 records there are just two with this behaviour. I have checked all the QUANTITY and LENGTH entries of these two records and they seem to be OK.

I deleted those two records from the database and reentered them personally to make sure that values are correct. But the result is still same. Really strange!

 

[cmmrfrds]

The double data type in Access is a floating point number and always an approximate. If you want to always reach zero then a non-floating point number data type is what you need.

 

[hansu]

Thanks cmmrfrds
That was exactly the problem. I changed the data type and it works fine now.