Results 1 to 7 of 7

Thread: query between

  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: query between

    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. #2
    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. #3
    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. #4
    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. #5
    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. #6
    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())))))
    (please check the ) 's
    EndCurrentYear is calculated in the same manner.

  7. #7
    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
  •