Discrepancy between query results, rounding error?

[Guest]

I have been writing a simple query in Access 2003 to count records with a
calculated value greater than a threshhold. I entered a few different values
for the threshold into the query and tabulated the values. Getting bored with
this I decided to use a table with the different threshholds in it and
generate all the counts at once. I started to get different result counts
than the ones I had tabulated manually.

Here is a cut down version...


Table(Values), 1 record

[Prob] [Price]
0.39 5


Table(Threshhold), 1 record

[Exp]
0.95

All the fields are doubles


Query1:

SELECT Values.Prob, Values.Price, [Prob]*[Price]-1 AS ExpCalc
FROM [Values]
WHERE ((([Prob]*[Price]-1)>0.95));

When I run Query1 I get no results as expected since 0.39 * 5 - 1 = 0.95


Query2:

SELECT Values.Prob, Values.Price, [Prob]*[Price]-1 AS ExpCalc,
Threshhold.Above
FROM [Values], Threshhold
WHERE ((([Prob]*[Price]-1)>[Threshhold].[Above]) AND
((Threshhold.Above)=0.95));

When I run Query2 I get one result:
[Prob] [Price] [ExpCalc] [Above]
0.39 5 0.95 0.95

I can see that a rounding error could give me a result, but why does this
not apply to both queries? Is there something else I am missing here?

Ian.

 

[Tom Ellison]

Dear Ian:

Probably datatypes. Are any of your columns floating point, single, or
double? These cannot store many decimal values exactly. So you get
approximations. Then, when you're right on the boundary, things don't
behave as expected. 0.95 may be stored as 0.949999961 (as an example).

Unless you work for NASA or at Las Alamos, forget the floating point,
please.

Tom Ellison

 

[Guest]

Dear Ian:

Probably datatypes. Are any of your columns floating point, single, or
double? These cannot store many decimal values exactly. So you get
approximations. Then, when you're right on the boundary, things don't
behave as expected. 0.95 may be stored as 0.949999961 (as an example).

Unless you work for NASA or at Las Alamos, forget the floating point,
please.

Tom Ellison

All the fields are doubles. I used doubles because some of the numbers in I
am processing have large decimal expansions. I think perhaps using Decimal
datatype will give me better results, haven't tried that yet.

What was strange to me was that both queries appeared to do the same thing.
Looking at this more closely it seem Query2 is pulling a double from the
database with value 0.95 and comparing this with CDbl(0.39)*CDbl(5)-CDbl(1).
So apparently:

CDbl(0.39)*CDbl(5)-CDbl(1) > CDbl(0.95) is true

Query1 is doing this comparison:

CDbl(0.39)*CDbl(5)-CDbl(1) > 0.95 which is false

This seems to be the same as CDbl(0.39)*CDbl(5)-CDbl(1) > CSng(0.95)


This is how various test expressions evaluate

CDbl(0.39)*CDbl(5)-CDbl(1) = CSng(0.95) is true
CDbl(0.39)*CDbl(5)-CDbl(1) > CDbl(0.95) is true
CDbl(0.39)*CDbl(5)-CDbl(1) > CSng(0.95) is false
CDbl(0.39)*CDbl(5)-CDbl(1) = CDbl(0.95) is false

Does the Jet Engine evaluate expressions/conversions the same way as VB? I
didn't find any documentation on this.

Ian.