you now see the issue with floating point numbers.
I see, how can I make it in the SELECT statement in Access that when the number are the same (no matter how big the numbers are) the difference is 0 ?
my bet is that they are not the same in excel either, but you have formatting so it appears to be 0. Try the same comparison in Excel as you are using in a query (a=b) and see if it returns trueif I put it in Excel and minus the two they are 0
the point is, they aren't the same, only what you can see/can be displayed is the same. The difference is very small, if you don't need that level of accuracy, then use the round function or a different datatype such as currency (which is limited to 4dp)how can I make it in the SELECT statement in Access that when the number are the same
In excel when I do a = b it returns true, and the difference is
0.0000000000000000000000000000
when I copy the return from the Access query, I get:
-0.0000000000000003330669073875
What is the source of these two number - is it SQL linked table or an access table, and is it a comparison of a DateTime by any chance?
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
No just type in any big random number manually.
As RuralGuy said - you are suffering from Floating Point differences - read here for some more detailed explanations http://www.consultdmw.com/floating-point-numbers.htm and here http://sqlanywhere.blogspot.co.uk/20...ing-point.html
Last edited by Minty; 02-21-2018 at 10:37 AM. Reason: Credited the wrong responder!
DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
Please use the star below the post to say thanks if we have helped !
↓↓ It's down here ↓↓
If you have no control over the table fields you can just use a function to eliminate your issue.
i.e. Convert the field to a string and then truncate the string down to the resolution you need, just for comparison.
Try using Decimal and specify scale and precision properties in the table.
Precision is the total number of digits in a number, including left and right of the decimal.
Scale is the number of digits allowed to the right of the decimal.
So 1234567.89 has a precision of 9.
A precision of 4 and scale of 2 would result in a number with a maximun value of 99.99
123456.789 has a scale of 3 and precision of 9.
Percent designations like 125.5% would require precision 4 and scale of 3 (not one) since it is actually stored as 1.255.
Precision has a maximum value of 28, but textboxes in forms allow only a max of 15 decimal places.
For small numbers like yours, try both scale and precision as 18.
RuralGay, could you show me an example ?
davegri - right now for the table I set all the fields with number as number, double, general and decimal point as 15 the maximum.
I see and understand the floating point problem, that the number can only be shown 16 decimals but because of it can be so detail and precise and if goes over this, the difference are very small that even though two number we see as equal and the same show very very tiny difference.
Did you try my suggestion in post #10?right now for the table I set all the fields with number as number, double, general and decimal point as 15 the maximum.
If you did, you would see that the difference between the two numbers you show in post #1 is ZERO.