By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,983 Members | 1,717 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
7 Replies


JConsulting
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

ADezii
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

JConsulting
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

ADezii
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

ADezii
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

Post your reply

Sign in to post your reply or Sign up for a free account.