Queries with large values produce incorrect multiplication results

[atkellyx]

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

[JConsulting]

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/

[atkellyx]

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

[ADezii]

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.

[JConsulting]

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.

[ADezii]

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.

[atkellyx]

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

[ADezii]

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.