INT() and FIX() returning the wrong value?

[Nathan]

I am using FIX to match the functionality of rounddown() from excel, like so:

FIX([value]*1000)/1000

if value is 1.2344 it returns 1.234
if value is 1.2349 it returns 1.234

the problem is that if [value] is already an integer, it is returning value-1:
if value is 23 it returns 22
if value is 74 it returns 73

[value] is itself a calculation, the product of 2 decimals. If I build the
query to simply be a table of integers the funtion works as expected.

Perplexed- any ideas for a solution or work around?

INT is returning the same values. Any ideas why this would be?

 

[John Spencer]

You are probably running into some floating point errors with your math.

Both Int and Fix remove the fractional part of number and return the
resulting integer value.

The difference between Int and Fix is that if number is negative, Int
returns the first negative integer less than or equal to number, whereas
Fix returns the first negative integer greater than or equal to number.
For example, Int converts -8.4 to -9, and Fix converts -8.4 to -8.

I don't see the results you see if I enter 23, 74, -23, or -74 into the
expression. I suspect that value is not what you think it is and that
is causing the problem.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================