473,703 Members | 2,323 Online

Calculating odd-numbered and even-numbered years

7 New Member
Hi, I am building a program to calculate vacations for employees. Can anyone show me how to write a VBA code to differentiate even-numbered (Eg 2005) years from odd - numbered(Eg.200 6) years.

Thank you
Nov 26 '07 #1
9 2980
joba
7 New Member
Hi, I am building a program to calculate vacations for employees. Can anyone show me how to write a VBA code to differentiate even-numbered (Eg 2005) years from odd - numbered(Eg.200 6) years.

Thank you

Joba
Nov 26 '07 #2
atkellyx
16 New Member
Hi, I am building a program to calculate vacations for employees. Can anyone show me how to write a VBA code to differentiate even-numbered (Eg 2005) years from odd - numbered(Eg.200 6) years.

Thank you
Expand|Select|Wrap|Line Numbers
1. if [yearToTest] mod 2 = 0 then
2.    msgbox "even year"
3. else
4.    msgbox "odd year"
5. end if
Cheers AKC
Nov 26 '07 #3
MikeTheBike
639 Recognized Expert Contributor
Hi, I am building a program to calculate vacations for employees. Can anyone show me how to write a VBA code to differentiate even-numbered (Eg 2005) years from odd - numbered(Eg.200 6) years.

Thank you

Joba
Hi

No sure what you are trying to do, but you could use the Mod operator, ie.

IIF(Year Mod 2 = 0,"Year is Even","Year is Odd")

??

MTB
Nov 26 '07 #4
joba
7 New Member
Hi

No sure what you are trying to do, but you could use the Mod operator, ie.

IIF(Year Mod 2 = 0,"Year is Even","Year is Odd")

??

MTB
Thank you for your responds.

What I want to achieve from this program is:
1. When queried for employees to take vacation this year (2007), which is an odd-numbered year, it should select employees who where recruited in odd-numbered years(E.g. 19987 or 1993 or 1801 or 2009)

2. Likewise, next year is an even-numbered year(2008) so the program should select all employees who were recruited in even-numbered years.

I have created a table called members table in my DB with DOE(date of enlistment) as one of is column. The vacation year is every 2 years from DOE for the employees and the query must be focused or based on this value.

Vacation for all employees for the following year is calculated each August. Therefore the vacation forecast for 2008, when querried from the member table , should show all members that where recruited in even-numbered years and thus will have vacation in 2008.

I hope thsi explains what I'm trying to achieve.

Thank you.
Nov 26 '07 #5
joba
7 New Member
What I want to achieve from this program is:
1. When queried for employees to take vacation this year (2007), which is an odd-numbered year, it should select employees who where recruited in odd-numbered years(E.g. 19987 or 1993 or 1801 or 2009)

2. Likewise, next year is an even-numbered year(2008) so the program should select all employees who were recruited in even-numbered years.

I have created a table called members table in my DB with DOE(date of enlistment) as one of is column. The vacation year is every 2 years from DOE for the employees and the query must be focused or based on this value.

Vacation for all employees for the following year is calculated each August. Therefore the vacation forecast for 2008, when querried from the member table , should show all members that where recruited in even-numbered years and thus will have vacation in 2008.

I hope thsi explains what I'm trying to achieve.

Thank you.

--------------------------------------------------------------------------------
Nov 27 '07 #6
MMcCarthy
14,534 Recognized Expert Moderator MVP
joba

I have merged the three threads on this question into one. It is against site rules to make multiple posts on the same question.

Nov 27 '07 #7
MikeTheBike
639 Recognized Expert Contributor
Hi

You could try a WHERE condition in the query
for ODD years use

WHERE IIF(Year([Date of Enlistment]) Mod 2 = 1,1,0) = 1

and for EVEN years

WHERE IIF(Year([Date of Enlistment]) Mod 2 = 1,1,0) = 0

??

MTB
Nov 27 '07 #8
joba
7 New Member
Thank you Mike. It Works perfectly.
Nov 27 '07 #9
MikeTheBike
639 Recognized Expert Contributor
Thank you Mike. It Works perfectly.
It's always a pleasure to help.

But reflecting on the code (and as I dislike redundant code) it can be simplified (the IIF() function is redundant!) ie

for ODD years use

WHERE Year([Date of Enlistment]) Mod 2 = 1

and for EVEN years

WHERE Year([Date of Enlistment]) Mod 2 = 0

and thanks for letting us know it worked

MTB
Nov 28 '07 #10