MODULUS ...I don't understand!

MODULUS ...I don't understand!

Post by Jeff » Sat, 09 Oct 2004 14:15:26

I have a query that calculates various dates. The "calc" field shows the
number of weeks from the "StartDate". The data might look something like

Date field Calc Field

1-1-04 0
1-2-04 0.07
1-3-04 0.14
1-14-04 1
1-15-04 1.07 etc

I only want to show the records that are whole numbers from the startdate.
So only the dates 1-1-04 and 1-14-04 will show. Any ideas on what I might
use a the criteria on the calc field?

I have tried adding MOD 2 in the calc field, Calc:(.....)mod 2. but this
does not work, I am getting all kinds of strange numbers. I guess I don't
understand Modulus. According to help this should show the remainder only.
If I could show just the remainder I could use "0" in the criteria.

MODULUS ...I don't understand!

Post by Sm9obiBWaW » Sat, 09 Oct 2004 14:31:02

The MOD function returns the remainder after an integer division. It's
really not appropriate for this purpose - there is no "Mod 1", and Mod 2 will
return 0 for any even number and 1 for any odd number.

In this case, I'd suggest a criterion on the calc field of


The Fix function truncates the number to just its integer portion, so
Fix(1.07) will be 1, and Fix(1) will also be 1, but only the latter will be

Note that if Calc is calculated in floating point, you may have problems
with roundoff error. The expression might LOOK like 1 but actually be
0.99999946 or 1.0000002; it will not be found using the above test. Using a
Currency datatype will help avoid this problem.

John W. Vinson/MVP


MODULUS ...I don't understand!

Post by Wayne Morg » Sat, 09 Oct 2004 14:51:19

To get the remainder try

Number - Int(Number)

If this isn't zero, then you don't have a whole number.

Wayne Morgan
MS Access MVP