1

I have a SELECT query with the arguments below to fill in a DataTable. The fields OpeningQuantity, QtyIn & QtyOut from tbWHProduct are all in MS Access Double format. Sometimes when calculating decimals is involved, somehow the POSBalance returns a long string of decimal points:

e.g. 0 + 2565.4 - 2515.1 returns -50.3000000000002

query = "SELECT ProductID, ProductName, Caption, UOM, RetailPrice, DiscPercent, DiscSum,
MinimumSellingPrice, OpeningQuantity+QtyIn-QtyOut AS POSBalance, Location FROM tbWHProduct
WHERE Disable = FALSE ORDER BY ProductName";

Please help me understand what caused this odd calculation quirk and how I would go about avoiding such problem. Thank you.

JH Tan
  • 9
  • 4
  • Access has the `Decimal` data type, which is not floating point and thus avoids this behaviour. – Erik A Aug 13 '18 at 14:54
  • Too bad the database I'm working on has too many fields in `Double` to permit switching to `Decimal`, I have figured out a bypass method by calling `Round(X,1)`. Thank you all. – JH Tan Aug 13 '18 at 15:18

0 Answers0