Determining Year Currency

[ErikRP]

I'm hoping someone can help me figure out a problem I need to solve.

At our company, plans are reviewed every 3 years after a plan is created, i.e. a plan created in 2011 would be reviewed in 2014. Most plans were created in either 2010, 2011, or 2012 however new plans can be created, albeit infrequently.

What I need to know is given the current year the plans due to be reviewed this year, the plans due to be reviewed next year, and the plans due to be reviewed in two years.

My first thought was to do some sort of math calculation, i.e. CURR YEAR / PLAN YR. Where the result is a whole number it would equal CURRENT YEAR, a result of *.66 would be NEXT YEAR, and a result of *.33 would be TWO YEARS. How I would code this, though, is beyond me.

Of course I might be overlooking a much simpler solution, and I'd appreciate suggestions.

 

[plog]

You want to use the MOD operator (http://office.microsoft.com/en-us/access-help/HV080756430.aspx). It returns the remainder after division (i.e. 9 MOD 6 = 3, 100 MOD 2 = 0).

What you would do is get the current year, subtract the plan creation year and get the remainder of that when divided by 3. If its 0, then that plan is included. In a query it would look like this:

ReviewThisYear: Iif(((Year(Date()) - Year([PlanCreationDate])) MOD 3) = 0, TRUE, FALSE)

 

[Cronk]

Or to extend this to cover future years as well
YearOfReview= Year(Date()) + 3 - ((Year(Date()) - Year(CreateDate)) mod 3)
and exclude records with a CreateDate in the current year.

 

[MarkK]

boolean data

And you never need to do this in an IIF() . . .

=IIF(BooleanExpression, True, False)

. . . because a BooleanExpression is, by definition, either True or False. The IIF() function, above, doesn't do anything. Just use . . .

=BooleanExpression

 

[ErikRP]

Thanks to everyone for their collective assistance. I haven't got even 5 minutes to give this a try today but I'll report back on my progress.

Thanks again!