Scott Marcus
marcus at tsstech.com
Mon May 1 12:24:40 CDT 2006
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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com NOTICE: This electronic mail transmission is for the use of the named individual or entity to which it is directed and may contain information that is privileged or confidential. If you are not the intended recipient, any disclosure, copying, distribution or use of the contents of any information contained herein is prohibited. If you have received this electronic mail transmission in error, delete it from your system without copying or forwarding it, and notify the sender of the error by replying via email or calling TSS Technologies at (513) 772-7000, so that our address record can be corrected. Any information included in this email is provided on an “as is” and “where as” basis, and TSS Technologies makes no representations or warranties of any kind with respect to the completeness or accuracy of the information contained in this email.