Rounding Down in a calculation field

[FFLKing]

Im building an expression where I am dividing fields by constants and adding them together. I need all answers always round down, so 19/20=0, 21/20=1

[dunc723]

Use the Int() function. It returns the integer portion of any value, but does not convert it to an Integer data type.

[ssanfu]

I need all answers always round down, so 19/20=0, 21/20=1

You could also use integer division.

Try this code:

Sub CD()
   Dim x As Single
   x = 19 / 20
   MsgBox "Normal division : 19 / 20 = " & x
   x = 19 \ 20
   MsgBox "Integer division : 19 \ 20 = " & x

   x = 21 / 20
   MsgBox "Normal division : 21 \ 20 = " & x

   x = 21 \ 20
   MsgBox "Integer division : 21 \ 20 = " & x

End Sub

[dunc723]

Use the Int() function. It returns the integer portion of any value, but does not convert it to an Integer data type.

Another consideration - do you have to handle negative numbers? The Int() function and "rounding down" give different results for negative values. Examples:

12/10 = 1.2
Rounded = 1
Rounded down = 1
Int(12/10) = 1

17/10 = 1.7
Rounded = 2
Rounded down = 1
Int(17/10) = 1

-17/10 = -1.7
Rounded = -2
Rounded down = -2
Int(-17/10) = -1

PLD

[FFLKing]

Thank you Int() works perfectly for the positive numbers. For negative numbers I had to get creative.

In the test I have run -7/10=-1,-17/10=-2 with Int().

I need -7/10=0,-17/10=-1

Here was my solution,
(IIf([RushingYards]<0,Int((-1*[RushingYards])/10)*-1,Int([RushingYards]/10)))

It works but it is cumbersome, add in all of the other stat categories I need to figure out points for and I am looking at one huge formula. This leads me into a followup question, is there a limit on characters or nested functions in Access?

[ssanfu]

If you use Integer division, you get the results you want:
Integer division : -7\10 = 0
Integer division : -7\10 = -1

Much simpler IMO.
From Help:

/ Operator Example

This example uses the / operator to perform floating-point division.

Dim MyValue
MyValue = 10 / 4 ' Returns 2.5.
MyValue = 10 / 3 ' Returns 3.333333.

---------------------------------------------------------

\ Operator Example

This example uses the \ operator to perform integer division.

Dim MyValue
MyValue = 11 \ 4 ' Returns 2.
MyValue = 9 \ 3 ' Returns 3.
MyValue = 100 \ 3 ' Returns 33.
----------------------------------------------------------

Here is the code I used if you want to test it with other numbers:

Sub Int_DivisionExample()
   Dim x As Single
   Dim y As Single
   
   x = -7 / 10
   y = -7 \ 10      '< integer divide
   MsgBox "Normal division : -7 / 10 = " & x & vbNewLine & vbNewLine & "Integer division : -7 \ 10 = " & y

   x = -17 / 10
   y = -17 \ 10     '< integer divide
   MsgBox "Normal division : -17 / 10 = " & x & vbNewLine & vbNewLine & "Integer division : -17 \ 10 = " & y
End Sub

Good luck with your project.........

[dunc723]

If you use Integer division, you get the results you want:
Integer division : -7\10 = 0
Integer division : -7\10 = -1

Much simpler IMO.
From Help:

.........

Sorry, I stand corrected!

PLD

[FFLKing]

I have tried using Integer division and it keeps giving me the error, "The Expression ([RushingYards]/10) cannot be used in a calculated column". I am assuming it only works in vba?

[ssanfu]

No, it also works in queries and calculated controls.
See attached dB for a query example. (only a table and query)