Floating Point Arithmetic in VBA vs. SQL Server
VBA and SQL Server do not perform floating-point operations the same way. Being aware of this difference could save you hours of frustration someday.
The issue I'm about to highlight is a corner case of a corner case.
Most Access developers will go their whole lives without ever needing this information. But that's OK. Part of my goal with this blog is to expose you to all the strange phenomena that lurk along the edges of the Microsoft Access world.
You merely need to know that this issue exists. So that someday–when you are facing a bug so bizarre that you are sure you are losing your mind–your unconscious will surface the concept I'm about to cover and offer it up as a possible explanation. "Floating point arithmetic," it will whisper. "Could that be the problem?"
In short, I want to reduce the number of unknown unknowns in your life.
Horseshoes, Hand Grenades, and Floating Point Arithmetic
"Close only counts in horseshoes, hand grenades [and floating point arithmetic]."
Floating point arithmetic is not exact. In fact, SQL Server explicitly categorizes the
real data types as "Approximate Numerics."
Note the following equivalencies between VBA and SQL Server data types:
While these are perfectly equivalent data types, the floating point arithmetic operations in VBA and SQL Server are not the same.
OK, enough lead-in. Here's the issue:
In other words, subtracting two explicitly-typed, single-precision floating-point numbers from each other and then coercing that difference to a double-precision floating-point number results in two different numbers depending on whether the calculation is performed in SQL Server or VBA.
And, to make things even more interesting, if we use the Access database engine expression service, we get a number that matches SQL Server (note that VBA displays the result in scientific notation):
Rounding exaggerates the problem
The difference between the two results above is less than two-billionths:
Unless you are a molecular scientist, a difference in two-billionths is unlikely to cause you any problems in practice.
But look what happens when we round each result to three decimal places:
Suddenly, the difference is one-thousandth. And that is a difference that will cause you some grief.
This is not a type conversion issue
Implicit type conversion is a different problem that I have written about in the past. Because I knew that could be an issue, I used the CDbl, CSng, and cast functions to eliminate the possibility of type conversion playing a role in the examples above.
The only explanation I can come up with is this:
VBA implements floating point arithmetic differently than SQL Server (and the Jet / ACE Expression service).