SQL Server

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.

Mike Wolfe

Sep 23, 2021 • 3 min read

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 float and real data types as "Approximate Numerics."

*float* and *real*: for when close enough is good enough.

Note the following equivalencies between VBA and SQL Server data types:

Double = float
Single = real

While these are perfectly equivalent data types, the floating point arithmetic operations in VBA and SQL Server are not the same.

Example, Please

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).