MODULUS ...I don't understand!

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

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
this:

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.
 
Jeff said:
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
this:

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.
Click to expand...

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

=Fix([calc])

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

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
 
To get the remainder try

Number - Int(Number)

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