Why is MS Access returning some results in scientific notation?-CodePudding

I have two fields, both have the size set to double in the table properties. When I subtract one field from the other some of the results are displayed as scientific notation when I click in the cell and others just show regular standard format to decimal places.

The data in both fields was updated with Round([Field01],2) and Round([Filed2],2) so the numbers in the fields should not be any longer than 2 decimal places.

Here's an example: Field1 = 7.01 Field2 = 7.00 But when I subtract Field1 from Field2 the access display shows 0.01 but when I click on the result it displays, -9.99999999999979E-03. So of course, when I try to filter on all results that have 0.01 the query comes back empty because it thinks the result is -9.99999999999979E-03.

Even stranger is if Field1 = 1.02 and Field2 = 1.00, the result is 0.02 and when I click on the result the display still shows 0.02 and I can filter on all results that equal 0.02.

Why would MS Access treat numbers in the same query differently? Why is it displaying in Scientific Notation and not filtering?

Thanks for any support.

CodePudding user response：

"The data in both fields was updated with Round([Field01],2) and Round([Filed2],2) so the numbers in the fields should not be any longer than 2 decimal places." instead of rounding up(which i think is the reason for the scientific notation) you can use number field as data type , then under field size choose double, then under decimal places choose 2.

CodePudding user response：

Take this simple code in Access (or even Excel) and run it!

Public Sub TestAdd()
   Dim MyNumber      As Single
   Dim I             As Integer
  For I = 1 To 10
      MyNumber = MyNumber   1.01
      Debug.Print MyNumber
  Next I

End Sub

Here is the output of the above:

You can see that after just 7 additions rounding is occurring!

Note how after JUST 7 simple little additions Access is now spitting out wrong numbers and has rounding errors!

More amazing? The above code runs the SAME in Excel!

Ok, I am sure I have your attention now!

If I recall, the FIRST day and first class in computing science? Computers don't store exact numbers when using floating point numbers.

So, then how is it possible that the WHOLE business community using Excel, or Access, or in fact your desktop calculator not come crashing down?

You mean Access cannot add up 7 simple little numbers without having errors?

How can I even do payroll then?

The basic concept and ALL you need to know here is that computers store real (floating) numbers only as approximate.

And integer values are stored exact.

so, there are several approaches here, and in fact if you writing ANY business software that needs to work with money values? And not suffer rounding errors?

Then you better off to choose what we called some kind of "scaled" integer. Behind the scenes, the computer does NOT use floating numbers, but uses a integer value, and the also has a "decimal" position.

In fact, in a lot of older business BASIC languages, or others? We often had to do the scaling on our own. (so, we would choose a large integer format). In fact, this "scaling" feature still exists in Access!!! (and you see it in the format options).

So, two choices here. If you don't want "tiny" rounding errors, then use "currency" data type. This may, or may not be sufficient for you, since it only allows a max of 4 decimal places. But in most cases, it should suffice. And if you need "more" decimal places, then you can multiply the values by 1000, and then divide by 1000 when done the calculations.

however, try changing the column type to currency and that should work. (this type of data is how your desktop calculator also works - and thus you not see funny rounding errors as a result (in most cases).

but, the FIRST rule of the day? First computer course?

Computers do not store exact numbers for floating point numbers - they are approximations, and are subject to rounding errors. Now, if you really are using double for the table, then I don't think these rounding errors should show up - since you have "so many decimal places" available.

But, I would try using currency data type - it is a scaled integer, or so called packed decimal.

You can ALSO choose to use a packed decimal in Access, and it supports out to 28 digits, and you can set the "scale" (the decimal point location). However, since you can't declare a decimal type in VBA, then I would suggest that in the table (and in VBA code, use currency data types).

If you need more then 4 decimal points, then consider scaling the currency in your code, or perhaps at that point, you consider using a packed decimal type in the table, but values in VBA will have to use the "variant" type, and they will correctly take on the data column setting if used in code and assigned a value from the table(s) in question.

Needless to say, the first day you start dealing with computers, and that first day ANYTHING beyond being a "end user"? Well, this is your first lesson of the day!