Mod

[Bryce]

I'm using the Mod function within access. My problem is
that I cant get it to pick up the numbers after the
decimal. For example 13 mod 10.5 should be 2.5. Access
returns 3. Any ideas?

 

[Wayne Morgan]

From the help file:

The modulus, or remainder, operator divides number1 by number2 (rounding
floating-point numbers to integers) and returns only the remainder as
result.

Note the part in parenthesis. Access does "bankers" rounding (rounding .5 to
the nearest even number) so the 10.5 is rounded to 10 then the Mod is
calculated. If you had 11.5 it would be rounded to 12 and your result would
be 1.

To get the result you want:

((13*10) Mod (10.5*10)) / 10

Adjust the multiplier/divisor to match the maximum number of decimals you'll
have.

 

[Matthias Klaey]

From the help file:

The modulus, or remainder, operator divides number1 by number2 (rounding
floating-point numbers to integers) and returns only the remainder as
result.
[...]

To get the result you want:

((13*10) Mod (10.5*10)) / 10

Adjust the multiplier/divisor to match the maximum number of decimals you'll
have.

here is a version that does not depend on the number of decimals:

Function ModReal(x As Double, y As Double) As Double

ModReal = x - y * Int(x / y)

End Function

eg. ModReal(13, 10.5) = 2.5

Greetings
Matthias Kläy