query between

[macamarr]

Hi I have a query for vacations. What I am trying to do is
run the query so it will calculate how many days of
vacation was used between the previous year and the
current year based on hire date. I use two tables for the
query. tblEmployees and tblvacation. tblEmployees carries
the hire date and tblvacation is used for vacation start
and end dates which also totals the days between start and
end. Hire date is what vacations are based on. But I need
to get the hire date to act as an anniversary date.


example:
If employee "A" was hired on 1-6-2000 I want to query
between 1-6-2003 and 1-6-2004. If employee "B" was hired
on 11-16-1995 I want to query between 11-16-2003 and
11-16-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.


Reasoning***
Vacation is based off the anniversary of the hire date.
Employee's must use vacation days prior to the anniversary
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 anniversary year if one should
challenge how much time remains.

 

[John Viescas]

SELECT ....
FROM tblEmployees INNER JOIN
tblVacations ON tblEmployees.EmployeeID = tblVacations.EmployeeID
WHERE tblVacations.VacationEnd >=
DateSerial(Year(Date()) -1, Month(tblEmployees.HireDate),
Day(tblEmployees.HireDate))
AND tblVacations.VacationStart <=
DateSerial(Year(Date()), Month(tblEmployees.HireDate),
Day(tblEmployees.HireDate))

Note that the above includes vacation records that cross the hire
anniversary date (hired on July 1, vacation from June 28 to July 7), so you
should be careful to include only those days within the range in your total
of vacation days for the "year."

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[macamarr]

Thank You, works like a champ!!!

-----Original Message-----
SELECT ....
FROM tblEmployees INNER JOIN
tblVacations ON tblEmployees.EmployeeID = tblVacations.EmployeeID
WHERE tblVacations.VacationEnd >=
DateSerial(Year(Date()) -1, Month(tblEmployees.HireDate),
Day(tblEmployees.HireDate))
AND tblVacations.VacationStart <=
DateSerial(Year(Date()), Month(tblEmployees.HireDate),
Day(tblEmployees.HireDate))

Note that the above includes vacation records that cross the hire
anniversary date (hired on July 1, vacation from June 28 to July 7), so you
should be careful to include only those days within the range in your total
of vacation days for the "year."

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.