> ## Content Index > Fetch the complete content index at: https://nolongerset.com/llms.txt > Use this file to discover other available public pages before exploring further. # Floating Point Arithmetic in VBA vs. SQL Server - URL: https://nolongerset.com/floating-point-arithmetic-in-vba-vs-sql-server/ - Published: 2021-09-24T01:53:24.000Z - Updated: 2026-05-08T13:02:14.000Z - Description: 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. - Author: Mike Wolfe - Tags: SQL Server, Advanced, #Import 2026-05-20 02:59 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](https://en.wikipedia.org/wiki/There%5Fare%5Fknown%5Fknowns) 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](https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15#approximate-numerics)." ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-68.png) *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: ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-70.png) ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-73.png) 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](https://nolongerset.com/expressions-vs-code/), we get a number that matches SQL Server (*note that VBA displays the result in scientific notation*): ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-72.png) ### Rounding exaggerates the problem The difference between the two results above is less than two-*billionths*: ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-74.png) 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: ![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-75.png) 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](https://nolongerset.com/expressions-vs-code-implicit-type-conversion/) 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).** --- ### External references [There are known knowns - Wikipedia![](https://en.wikipedia.org/static/apple-touch/wikipedia.png)Wikimedia Foundation, Inc.Contributors to Wikimedia projects![](https://upload.wikimedia.org/wikipedia/commons/thumb/2/27/Defense.gov_News_Photo_020221-D-9880W-080.jpg/1200px-Defense.gov_News_Photo_020221-D-9880W-080.jpg)](https://en.wikipedia.org/wiki/There%5Fare%5Fknown%5Fknowns) [Data types (Transact-SQL) - SQL ServerThis article provides a summary of the different data types available in SQL Server.Microsoft DocsMikeRayMSFT![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver15#approximate-numerics) ### Referenced articles [Expressions vs. CodeWhen is code not code? When it’s an expression. What’s the difference and who really cares? Let’s explore.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2020/10/goats-173940_1280.jpg)](https://nolongerset.com/expressions-vs-code/) [Expressions vs. Code: Implicit Type ConversionIn Microsoft Access, is a whole number literal implicitly treated as a Long or an Integer or a Double? As it turns out, it depends on where you ask.![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/pexels-chris-wynn-6402320.jpg)](https://nolongerset.com/expressions-vs-code-implicit-type-conversion/) *Image by [mycol](https://pixabay.com/users/mycol-6532168/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=2797541) from [Pixabay](https://pixabay.com/?utm%5Fsource=link-attribution&utm%5Fmedium=referral&utm%5Fcampaign=image&utm%5Fcontent=2797541)*