MS Access 2013 and signed zeros

We all know the “IEEE Standard for Floating-Point Arithmetic (IEEE 754)” – Or at least we should. This technical standard allows signed zeros – especially when values are rounded. Well, let’s see what can happen there.

First, let’s build some data with Access-SQL:

SELECT Value_
FROM (SELECT TOP 1 0.01 AS Value_ FROM MSysObjects
UNION ALL
SELECT TOP 1 -0.01 FROM MSysObjects
UNION ALL
SELECT TOP 1 1.01 FROM MSysObjects
UNION ALL
SELECT TOP 1 0.99 FROM MSysObjects) AS [%$##@_Alias];

This results in the following data set:

Now let’s round the values to integers:

SELECT Value_, round(Value_,0) AS Value_round
FROM (SELECT TOP 1 0.01 AS Value_ FROM MSysObjects
UNION ALL
SELECT TOP 1 -0.01 FROM MSysObjects
UNION ALL
SELECT TOP 1 1.01 FROM MSysObjects
UNION ALL
SELECT TOP 1 0.99 FROM MSysObjects) AS [%$##@_Alias];

The result will not surprise anyone:

And now we do a GROUP BY on the rounded integers (and count the values):

SELECT Round(Values_,0) AS Value_round, Count(Round([Values_],0)) AS NumberOf
FROM (SELECT TOP 1 0.01 AS Values_ FROM MSysObjects
UNION ALL
SELECT TOP 1 -0.01 FROM MSysObjects
UNION ALL
SELECT TOP 1 1.01 FROM MSysObjects
UNION ALL
SELECT TOP 1 0.99 FROM MSysObjects) AS [%$##@_Alias]
GROUP BY Round(Values_, 0);

The one or the other may not have expected this result:

A DISTINCT gives the same results:

SELECT DISTINCT Round(Value_,0) AS Value_round
FROM (SELECT TOP 1 0.01 AS Value_ FROM MSysObjects
UNION ALL
SELECT TOP 1 -0.01 FROM MSysObjects
UNION ALL
SELECT TOP 1 1.01 FROM MSysObjects
UNION ALL
SELECT TOP 1 0.99 FROM MSysObjects) AS [%$##@_Alias];

And the output:

But maybe it will be clearer if you copy the data via copy&paste to xl:

So that’s the point: MS Access uses signed zeros – but doesn’t display them, and distinguishes them in SQL – But not in VBA:

xl also distinguishes the zero from the negative zero – but displays the negative zero: