[AccessD] Rounding in SQL Server ODBC

Gustav Brock Gustav at cactus.dk
Mon May 1 11:53:32 CDT 2006


Hi all

The SQL Server ODBC driver sometimes does some funny rounding when handling doubles:

ID	RoundedID
0	0
1	0
2	3
3	3
4	3
5	7
6	7
7	7
8	7
9	10
10	10
11	10
12	13
13	13
14	13
15	17
16	17
17	17
18	17
19	20
...
990	990
991	990
992	993
993	993
994	993
995	997
996	997
997	997
998	997
999	0

These are the last decimal part of Doubles. As you can see, they are rounded by 3, sort of.
I need to simulate this rounding in Access SQL and came up with this solution using Sgn() and integer division:

.. (([ID]\10)*10
  +Sgn(([ID] Mod 10)\2)*3
  +Sgn(([ID] Mod 10)\5)*4
  +Sgn(([ID] Mod 10)\9)*3) 
  Mod 1000 AS RoundedID

The value must not exceed 1000, that's what the "Mod 1000" does.
This works exactly as I wish but it looks clumsy. 
Would anyone have a simpler method? The tricky part is, that 7 is present four times per ten elements while 0 and 3 each only count for three; this breaks a sequence of integer dividing by 3.

/gustav




More information about the AccessD mailing list