1. Registered User
Join Date
Jan 2004
Posts
9

Hi I have a query for vacations. What I am trying to do is run the query
so it will calculate how vacation was used between the previous year and the current year based on hire date.

Presently I am only able to do for the last year. Between Now() And Now()-365

What I want to do is

If I was hired on 1-6-2000 I want to query between 1-6-2003
and 1-6-2004. I know I can type this in for the query each time I run it, but I was hoping to find a way where I have it detect the date of hire and agianst the the current year while utilizing the day and the month to set the criteria.

Vacation is based off the anniversary of the hire date. Employee's must use vacation days prior to the anninversary of the following year or they lose it. So I need to be able run this query to show all the employees vacation detail for the previous year if one should challenge how much time remains.

2. Registered User
Join Date
Dec 2003
Posts
454
How about the employee who was hired on 10/10/2000? Do you want to know his vacation info between 10/10/2002 and 10/10/2003?

3. Registered User
Join Date
Jan 2004
Posts
9
Yes that would be correct, I would want to query every employee for the previous years vacation regardless of hire date (anniversary) but based off of anniversary date.
Last edited by macamarr; 02-22-04 at 19:04.

4. Registered User
Join Date
Dec 2003
Posts
454
1. get the employees who were hired before Now() - 365.
2. get the MONTH and DAY from the hired date.
3. compare the date between MONTH/DAY/2004 and Now()
if MONTH/DAY/2004 > Now(), the start date is MONTH/DAY/2002 and the end date is MONTH/DAY/2003. otherwise, the start date is MONTH/DAY/2003 and the end date is MONTH/DAY/2004.

5. Registered User
Join Date
Jan 2004
Posts
9
I appreciate your help but unfortunately I am a self-taught beginner and frankly, I don’t have a clue how to do what you suggest. I get lost on #2 and #3.

6. Registered User
Join Date
Jan 2004
Posts
3
Originally posted by macamarr
I appreciate your help but unfortunately I am a self-taught beginner and frankly, I don’t have a clue how to do what you suggest. I get lost on #2 and #3.
How to get a day and a month form a date (you can use this in a query):
<field with date> should be the field with the date the employee entered the company.

Field1: Month: DatePart("m",<field with date>)
Field2: Day: DatePart("d",<field with date>)

Field3: BeginCurrentYear: iif(CDate(DatePart("d",<field with date>) & "/" & DatePart("m",<field with date>) & "/" & DatePart("yyyy",now())) > now(); CDate((DatePart("d",<field with date>) & "/" & DatePart("m",<field with date>) & "/" & (DatePart("yyyy",now())-1))) ;CDate((DatePart("d",<field with date>) & "/" & DatePart("m",<field with date>) & "/" & DatePart("yyyy",now())))))
EndCurrentYear is calculated in the same manner.

7. Registered User
Join Date
Jan 2004
Posts
9
Thank you and I'll give it a go...

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•