Int or Fix function problem

[Jake]

Hi,
I have data from a calculation that needs to be rounded down. It is
[Payment]/[Rate]. This gives me a number of contracts paid. I need # of
contracts fully paid, so 6.9 would be 6 contracts. I have used the formulas
Int([Payment]/[Rate]) and Fix([Payment]/[Rate]). For the most part they work
fine, but I am having an issue with some of the values going down a whole
integer. A value of 7 will show up as 6, or a value of 113 is showing as
112. I have even tried updating fields in a table with the calculation
result, then using Int and Fix on just that field, and have the same issue of
losing a whole number. Please help!! Thanks

 

[John Spencer MVP]

I suspect that if you are seeing 7 the calculation is probably actually
returning something like 6.99999999902. That will be set to 6 although it
could show up as 7.000 depending on how things are formatted.

You could try an expression like the following to see if you get the desired
results.
Int([Payment]/[Rate] + .00001)

Or try
Int(CCur([Payment]/[Rate]))
as another alternative.


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

 

[Hans Up]

Hi,
I have data from a calculation that needs to be rounded down. It is
[Payment]/[Rate]. This gives me a number of contracts paid. I need # of
contracts fully paid, so 6.9 would be 6 contracts. I have used the formulas
Int([Payment]/[Rate]) and Fix([Payment]/[Rate]). For the most part they work
fine, but I am having an issue with some of the values going down a whole
integer. A value of 7 will show up as 6, or a value of 113 is showing as
112. I have even tried updating fields in a table with the calculation
result, then using Int and Fix on just that field, and have the same issue of
losing a whole number. Please help!! Thanks

What happens if you use integer division instead of floating point division?

In other words, instead of:

[Payment]/[Rate]

Use:

[Payment]\[Rate]

Perhaps that would eliminate the need to apply Int or Fix. A side
benefit is that integer division is significantly faster than floating
point division. Naturally you'd have to do a heck of a lot of dividing
to actually notice the difference, but at least integer division
shouldn't slow down your code.

 

[John Spencer]

Well, you can get different results.
?Int(1.92/2) returns 0
? 1.92\2 returns 1

Before division is performed, the numeric expressions are rounded to
Byte, Integer, or Long expressions.

Usually, the data type of result is a Byte, Byte variant, Integer,
Integer variant, Long, or Long variant, regardless of whether result is
a whole number. Any fractional portion is truncated. However, if any
expression is Null, result is Null. Any expression that is Empty is
treated as 0.

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

Hi,
I have data from a calculation that needs to be rounded down. It is
[Payment]/[Rate]. This gives me a number of contracts paid. I need #
of contracts fully paid, so 6.9 would be 6 contracts. I have used the
formulas Int([Payment]/[Rate]) and Fix([Payment]/[Rate]). For the
most part they work fine, but I am having an issue with some of the
values going down a whole integer. A value of 7 will show up as 6, or
a value of 113 is showing as 112. I have even tried updating fields
in a table with the calculation result, then using Int and Fix on just
that field, and have the same issue of losing a whole number. Please
help!! Thanks

What happens if you use integer division instead of floating point
division?

In other words, instead of:

[Payment]/[Rate]

Use:

[Payment]\[Rate]

Perhaps that would eliminate the need to apply Int or Fix. A side
benefit is that integer division is significantly faster than floating
point division. Naturally you'd have to do a heck of a lot of dividing
to actually notice the difference, but at least integer division
shouldn't slow down your code.

 

[Hans Up]

Well, you can get different results.
?Int(1.92/2) returns 0
? 1.92\2 returns 1

Thank you, John. That is sure not what I was expecting, so I need to go
check some stuff. :-0

Regards,
Hans

 

[Jake]

Hi John,
Thanks a lot, I used Int(CCur([Payment]/[Rate])) and it fixed my problem.

I suspect that if you are seeing 7 the calculation is probably actually
returning something like 6.99999999902. That will be set to 6 although it
could show up as 7.000 depending on how things are formatted.

You could try an expression like the following to see if you get the desired
results.
Int([Payment]/[Rate] + .00001)

Or try
Int(CCur([Payment]/[Rate]))
as another alternative.


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

Hi,
I have data from a calculation that needs to be rounded down. It is
[Payment]/[Rate]. This gives me a number of contracts paid. I need # of
contracts fully paid, so 6.9 would be 6 contracts. I have used the formulas
Int([Payment]/[Rate]) and Fix([Payment]/[Rate]). For the most part they work
fine, but I am having an issue with some of the values going down a whole
integer. A value of 7 will show up as 6, or a value of 113 is showing as
112. I have even tried updating fields in a table with the calculation
result, then using Int and Fix on just that field, and have the same issue of
losing a whole number. Please help!! Thanks