Problem with Int( ) function 1

[SBerthold]

Why doesn't this produce the expected results:

?Int(4.123455 * (10 ^ 5) + 0.5)

produces: 412345

While:

4.123455*(10^5) +0.5

produces: 412346

I thought that the Int() function not supposed to round but "chop off" the fractional part of a number. But here, there is nothing to round - the result Prior to the Int() function is a Whole number!

Therefore, I get with:

x = (4.123455*(10^5) +0.5)

?Int(x)

a result of 412346

So, is this another unreliable VB function?

(PS. I know VB uses mathematical rounding - last digit must be an even number - but this shouldn't even need to round, the result prior to the Int() function is clearly a whole number)

 

[zemp]

Try using

CLng(4.123455 * (10 ^ 5) + 0.5)

Convert using a long data type, I think that your value is too large for an integer type. Thanks and Good Luck!

zemp

 

[SBerthold]

Thanks for the fast response!

That doesn't work either.
The reason for the Int() function is because it doesn't round. The CInt() or CLng() functions round.

?CLng(4.123454 * (10 ^ 5) + 0.5) = 412346

?4.123454 * (10 ^ 5) + 0.5 = 412345,9
So I am looking for 412345 in this example.

The number can be any fraction. So, using a string manipulation function will probably not work either (there could be many digits after the decimal)

 

[vb5prgrmr]

?formatnumber(4.123454 * (10 ^ 5) + 0.5, 0)
412,346

?formatnumber(4.123454 * (10 ^ 5), 0)
412,345

 

[vb5prgrmr]

?formatnumber(4.123454 * (10 ^ 5), 0 ,vbFalse,vbTrue,vbFalse)
412345

 

[CajunCenturion]

What is probably happening is that type casting is happening on the intermediate results. This often happens depending on the type of variable used to hold the final result. Force the calculations to happen in either Double or Single precision, to eliminate this undesirable side effect. You might try the following:

Int(CDbl(4.123455 * (10 ^ 5) + 0.5))

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[vb5prgrmr]

I see your problem now I did not understand what your were looking for to begin with. Instead of using the Int function try the Fix function (documentation as follows).

Remarks
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.

Fix(number) is equivalent to:

Sgn(number) * Int(Abs(number))
The following examples illustrate how the Int and Fix functions return integer portions of numbers:

MyNumber = Int(99.8) ' Returns 99.
MyNumber = Fix(99.2) ' Returns 99.
MyNumber = Int(-99.8) ' Returns -100.
MyNumber = Fix(-99.8) ' Returns -99.
MyNumber = Int(-99.2) ' Returns -100.
MyNumber = Fix(-99.2) ' Returns -99.

 

[SBerthold]

All the responses do not work:

CajunCenturion:
?Int(CDbl(4.989195 * (10 ^ 5) + 0.5))
Returns 4.98919 instead of 4.98920

vb5prgrmr:
And, with the fix, there is same problem. If you do it the way that you showed, it works, but when you add a calculation in the formula, it doesn't.

I would like to use a calculation like this in an SELECT statement for use with JET VBA, otherwise, I would just break the calculation apart. And, need to keep it as short as possible (with-out use of alot of slow IIF() functions).

 

[CCLINT]

Then use CSng or CDec (better).
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[CCLINT]

Oh, by the way the Int() function has nothing to do with the Integer var type, so a double can be used as well. Integer only means a whole number and has no limits on size. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[SBerthold]

It Works!

 

[CCLINT]

>Then use CSng or CDec (better).

That is, if the number is not too large. See the limitation on CDec() function in your help files.
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}