Gustav Brock
Gustav at cactus.dk
Tue May 2 02:42:12 CDT 2006
Hi Scott Well, it could be, and I considered some IIf() constructs too, but they got too long-winded. Further, your suggestion doesn't return the expected output beyond 5: ID Sgn IIf 0 0 0 1 0 0 2 3 3 3 3 3 4 3 3 5 7 6 6 7 6 7 7 6 8 7 9 9 10 9 10 10 9 11 10 12 12 13 12 13 13 12 14 13 15 /gustav >>> marcus at tsstech.com 01-05-2006 19:24 >>> I don't know if this is any better... ID + iif((ID mod 3) <= 1,0,3) - (ID mod 3) Scott Marcus Computer Programmer TSS Technologies Inc. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Monday, May 01, 2006 12:54 PM To: accessd at databaseadvisors.com Subject: [AccessD] Rounding in SQL Server ODBC 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