Subtraction Problem

[Alex Motilal]

I have a Database which deals with the purchase of raw cotton and processing. In the table tblPurchase among various fields I have DateOfPurchase field and QtyPurchsed field. In the table tblGinning I do have DateOfGinning and QtyGinned fields among other fields.


I do have a query qryGinning status. It pulls data from both the tables I have mentioned. The query calculates the total purchases and total ginned. In the calculated field which calculates the Balance Quantity available, for some records it gives the following type of results:

Purchased Qty:1602.65
Ginned Qty :1602.65

Balance Qty :-2.27373675443232E-13 (Actual result should be 0)

Among 24 records 6 records give such results instead of 0. All other records are correct.

I will be very thankful if someone gives a solution.

Regards
Alex

[NTC]

Issue 1: If you are seeing the Balance Qty info in a form or report object - sanity check by stretching that box to be really wide. One of the quirks of Access is that if all the data cannot fit in a display field it will instead display an unhelpful piece of information like this (or xxxx depending on how the db has been optioned_

Issue 2: The reason one is not getting 0 is because there is unseen decimals. I won't describe this as a quirk but it is confusing. What is stored in a table and what is viewed can be 2 different things.... particularly in regard to decimal places. In tables themselves - put your cursor in the field of one of these and you will probably then see it expand to display a lot more decimal places than you realize. Typically this is due to there have been some divisional math.

The fix to issue 2 depends on where you are in the development of your database. If it is brand new and you still can alter your field properties, and delete test records and start from scratch - then it may be able to be dealt with at the table level. Otherwise one must get familiar with the Round function and apply rounding to whatever decimal level is appropriate for your business.

[Alex Motilal]

Thanks NTC
The result shows in the Query itself. I am using Round function to set right. However the problem is when I try to retrieve data using criteria. For example if I want to know the balance quantity available above 100 quintals it gives the wrong results. Both the Purchase & Ginning Quantity is number field with two decimals. The peculiar thing is it is only adding and subtracting.