431,983 Members | 1,717 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,983 IT Pros & Developers. It's quick & easy.

# Queries with large values produce incorrect multiplication results

 P: 16 Hi I have often found over the year odd maths stuff that queries can return (eg 1*5 = 4.9999999999999999). I have always got around them but have a problem now which has me stumped. To demonstrate this, this query: SELECT contractWorkOrderActivity.qty, contractWorkOrderActivity.rate, CDbl(CDbl([qty])*CDbl([rate])) AS [Double], [qty]*[Rate] AS Native, CSng(CSng([qty])*CSng([rate])) AS [Single] FROM contractWorkOrderActivity; Very straightforward. When I apply this query to a table with these values: qty 121212100 rate 77.8 I get the following results. qty..........121212100 rate.........77.8 Double.....9430303305.91003 Native......9430303744 Single......9430303744 The problems are two fold. 1. Why do I get different results from a Single and Double conversion 2. More importantly, why are none of them correct? The correct answer is 9430301380, and the difference is significant. Env is Access 2003 on XP Pro. If anyone could cast any light on this I would be greatly appreciative. Thanks Anthony May 13 '07 #1
7 Replies

 Expert 100+ P: 603 Hi I have often found over the year odd maths stuff that queries can return (eg 1*5 = 4.9999999999999999). I have always got around them but have a problem now which has me stumped. To demonstrate this, this query: SELECT contractWorkOrderActivity.qty, contractWorkOrderActivity.rate, CDbl(CDbl([qty])*CDbl([rate])) AS [Double], [qty]*[Rate] AS Native, CSng(CSng([qty])*CSng([rate])) AS [Single] FROM contractWorkOrderActivity; Very straightforward. When I apply this query to a table with these values: qty 121212100 rate 77.8 I get the following results. qty..........121212100 rate.........77.8 Double.....9430303305.91003 Native......9430303744 Single......9430303744 The problems are two fold. 1. Why do I get different results from a Single and Double conversion 2. More importantly, why are none of them correct? The correct answer is 9430301380, and the difference is significant. Env is Access 2003 on XP Pro. If anyone could cast any light on this I would be greatly appreciative. Thanks Anthony Give this a read over. http://support.microsoft.com/kb/q42980/ May 13 '07 #2

 P: 16 Thanks, that was an interesting read. I did notice the discrepancy on test data and data with numbers this big is unlikely to occur for multiplication but the discrepancy is significant, in the case I gave it was over \$1500. So it is not possible to avoid this? Excel and calc.exe don't have the problem, so is it something to do with the db engine and the data types it can/chooses to use? I can't give a client and app that generates invoicing totals for them without certainty of accuracy (very small errors and rounding discrepancies are expected of course). Thanks Anthony May 13 '07 #3

 Expert 5K+ P: 8,628 Hi I have often found over the year odd maths stuff that queries can return (eg 1*5 = 4.9999999999999999). I have always got around them but have a problem now which has me stumped. To demonstrate this, this query: SELECT contractWorkOrderActivity.qty, contractWorkOrderActivity.rate, CDbl(CDbl([qty])*CDbl([rate])) AS [Double], [qty]*[Rate] AS Native, CSng(CSng([qty])*CSng([rate])) AS [Single] FROM contractWorkOrderActivity; Very straightforward. When I apply this query to a table with these values: qty 121212100 rate 77.8 I get the following results. qty..........121212100 rate.........77.8 Double.....9430303305.91003 Native......9430303744 Single......9430303744 The problems are two fold. 1. Why do I get different results from a Single and Double conversion 2. More importantly, why are none of them correct? The correct answer is 9430301380, and the difference is significant. Env is Access 2003 on XP Pro. If anyone could cast any light on this I would be greatly appreciative. Thanks Anthony Do not perform any Explicit Data Conversions - simply set the Data Types of both [qty] and [rate] in the underlying Table to DOUBLE and the correct answer (9430301380) will be generated. May 13 '07 #4

 Expert 100+ P: 603 Do not perform any Explicit Data Conversions - simply set the Data Types of both [qty] and [rate] in the underlying Table to DOUBLE and the correct answer (9430301380) will be generated. From the VBA help file: Decimal data type A data type that contains decimal numbers scaled by a power of 10. For zero-scaled numbers, that is, numbers with no decimal places, the range is +/-79,228,162,514,264,337,593,543,950,335. For numbers with 28 decimal places the range is +/-7.9228162514264337593543950335. The smallest non-zero number that can be represented as a Decimal is 0.0000000000000000000000000001. Note that at this time the Decimal data type can only be used within a Variant. You cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function. Currency data type A data type with a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Use this data type for calculations involving money and for fixed-point calculations where accuracy is particularly important. The at sign (@) type-declaration character represents Currency in Visual Basic. May 13 '07 #5

 Expert 5K+ P: 8,628 From the VBA help file: Decimal data type A data type that contains decimal numbers scaled by a power of 10. For zero-scaled numbers, that is, numbers with no decimal places, the range is +/-79,228,162,514,264,337,593,543,950,335. For numbers with 28 decimal places the range is +/-7.9228162514264337593543950335. The smallest non-zero number that can be represented as a Decimal is 0.0000000000000000000000000001. Note that at this time the Decimal data type can only be used within a Variant. You cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function. Currency data type A data type with a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807. Use this data type for calculations involving money and for fixed-point calculations where accuracy is particularly important. The at sign (@) type-declaration character represents Currency in Visual Basic. The Decimal Data Type will not generate the correct result. May 13 '07 #6

 P: 16 Do not perform any Explicit Data Conversions - simply set the Data Types of both [qty] and [rate] in the underlying Table to DOUBLE and the correct answer (9430301380) will be generated. Thanks I thought I had done that, but then I tried so many options perhaps I got confused, or perhaps the linked tables .... or something. Seems to work now thanks. The explicit type conversions were for demonstration purposes only, I don't normally do that. Thanks for you help May 14 '07 #7

 Expert 5K+ P: 8,628 Thanks I thought I had done that, but then I tried so many options perhaps I got confused, or perhaps the linked tables .... or something. Seems to work now thanks. The explicit type conversions were for demonstration purposes only, I don't normally do that. Thanks for you help You were correct in your initial assumptions, fields like Quantity and Rate should not be stored internally as DOUBLE Precision Numbers, except in this case, of course. May 14 '07 #8 