Strange Rounding in Query

[LHWC]

I have a simple expression in a query: [FW]-2.5. The values for [FW] have up to four decimal places. They display this way in the query, but the results of the expression, regardless of the decimal display, drop any values after the first decimal. For example:
[FW]=40.375
Expr=37.800
If I rewrite the expression: [FW]-2-.5, the expression displays correctly: 37.875.
Can anyone explain this? Thanks in advance!

 

[barny2006]

what version of access are you using? i tried both these ways that you mentioned, in access 2003, and i got the correct answer of 37.875

 

[LHWC]

I'm using 2002. I have used this query for a couple of years with perfect results. This anomaly has just occurred.

 

[barny2006]

how are you using this? in a query? is the query an update? what's the field defined as? single precision? double? or what?

 

[LHWC]

The data source for the query is another query, who's data source is a linked Excel SS. The field in the table and both queries displays to the appropriate number of decimals. Changing the format in Excel has no effect.

 

[MallardVHS]

LHWC: i understand about changing the excel format having no effect, but..

have you experimented with excel's "precision as displayed" function (under Tools / yada yada)

Also: have you looked into which app opens first (Access or Excel) and how many users each have?

Don

[green]Tis far easier to keep your duck in a row if you just have the one.[/green]

 

[PaulBricker]

Have you upgraded your Access or VB lately. I just discoverd that an FAQ I wrote when I was using A2000 no longer works in A2003 (2000 format). In doing some checking, I noticed that my old version of 2000, now on a stand alone, used VB 6.0. My new Access uses VB 6.3. What I saw was this.

Int((123.455 + .005)*100)/100
returns
123.45

the old version returns
123.46

which is what it should return. The problem seems to be in the Int() function. In 2003 the Int() acts like this.

Int((123.455 + .005)*100) return
12345

In 2000 it returns
12346

I don't know why, but apparently VB6.0 and VB6.3 act differently. I have not found any specific info on it yet, but I'm looking.

Paul

 

[PHV]

Paul, I guess that the diff between 6.3 and 6.0 is more likely the floating point stuff.
Have you tried this ?
Int(CDec(123.455 + .005)*100)/100

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PaulBricker]

Using CDec did change the results, thanks. I had not visited any floating point discussions in a while but spent some time this morning rereading a few old articles I had saved. I ran some of the samples they used for illustrations, for example,

Sum = 0
For i = 1 to 1000
Sum = Sum + .005
Next
Debug.Print Sum

Both 6.0 and 6.3 returned

4.99999999999992

and they returned the same for some other ones as well, but do not return the same value for the expressions in my FAQ. I appreciate your suggestion to use CDec. At least I can modify my FAQ so I'm not stressing over some persons bank balance.

Paul