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
 
Share this Question
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/
 
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
  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.
  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 zeroscaled 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 nonzero 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 fixedpoint calculations where accuracy is particularly important. The at sign (@) typedeclaration character represents Currency in Visual Basic.
  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 zeroscaled 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 nonzero 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 fixedpoint calculations where accuracy is particularly important. The at sign (@) typedeclaration character represents Currency in Visual Basic.
The Decimal Data Type will not generate the correct result.
 
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
  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.
    Question stats  viewed: 2041
 replies: 7
 date asked: May 13 '07
