Access VBA Error Getting Int() Value of Division

Question

Don't know where I am missing, but the simple Access VBA snippet below returns 2 when I divide any floating numbers that the division equals 3.

Note: I have received the code with this Int() function, but if changes are necessary to achieve same result, I can change anything.

Sub RoundTest()

    Dim a, b, c

    a = 4.8
    b = 1.6

    c = Int(a / b)

    MsgBox (c)

End Sub

Have any of you ever heard of something like this before? I tried on two different computers and get the same result. Maybe it is something with the Access compiler, or my code.

score 0 · Answer 1 · answered Mar 24 '17 at 17:11

0

The problem is that your Dim statement is not creating floating point numbers. They are all variant data types. In order to have it work properly you need to modify it as follows:

Sub RoundTest()
    Dim a As Single, b As Single, c As Single

    a = 4.8
    b = 1.6
    c = Int(a / b)

    MsgBox (c)
End Sub

answered Mar 24 '17 at 17:11

PKatona

629
2
9
19

That worked as well! :D Thank you so much!! – Mar 24 '17 at 17:22
2

This has nothing to do with using `Variant` data types. The OP's literals will both be promoted to `Double`, as will the result of `a / b` prior to being passed to `Int`. You're actually doing a *narrowing* cast when you assign to `Single`, and the only reason it works is because you're lowering the precision of the calculation. – Comintern Mar 24 '17 at 19:13
Then why does it give an unexpected answer if you do just "Dim a, b, c"? If that declaration is used, it comes back with 2, I tested it before I even tried to figure out a solution. Changing it to single (or double) returns 3, or using a Cint. So obviously the promotion of a variant by the Int op is doing something unexpected that is solved by declaring the vars as the actual type they are (actually, c should have been an int). – PKatona Mar 27 '17 at 14:22

score 0 · Answer 2 · edited Mar 24 '17 at 17:22

0

Using CInt() instead of Int() will give 3 as answer.

edited Mar 24 '17 at 17:22

CallumDA

12,025
6
30
52

answered Mar 24 '17 at 17:14

June7

19,874
8
24
34

That also worked :D Thanks a lot!! – Mar 24 '17 at 17:21

score 0 · Answer 3 · answered Mar 24 '17 at 17:52

You should always use Currency or Decimal for precision handling of decimals:

Sub RoundTest()

    Dim a As Currency
    Dim b As Currency
    Dim c As Currency

    a = 4.8
    b = 1.6

    c = Int(a / b)

    MsgBox c   ' Returns 3.

End Sub

Note, that it is floating point errors, not Int, that causes your "issue".

Access VBA Error Getting Int() Value of Division

3 Answers3