hello there,

I'm working on a database that records peoples holiday leave. I want to create a query, where I can ask the database who on holiday in a particular Month, or between two stated dates (i'll need two queries I think). My colums have two date sources: Start Date, End Date.

I've tried using the Between[] And[] Function, but it misses people who are Start their holiday before the Between date, but finish before the And date ( i hope that makes sense).

Is there anyway of capturing this info?

If possible, I'd like a query that askes for a specific Month, so that if someones leave falls within that month, it will capture it also. Is this possible?

Many thanks!
e1979

You can use the following parameter query which uses the datepart function:

SELECT employee, hstart, hend
FROM holiday
where [Enter Month] = datepart("m",[hstart]) or [Enter Month] = datepart("m",[hend])

This will give you the employee's name and beginning and ending dates of their holiday, if they took a holiday in a month you specify. [hstart] is the holiday start date and [hend] is the holiday end date. I tried it out with the following data.

id employee HSTART HEND
1 joey 05/01/2005 05/04/2005
2 monica 04/29/2005 05/02/2005
3 chandler 05/31/2005 06/05/2005
4 ross 05/06/2005 05/16/2005
5 phoebe 06/06/2005 06/09/2005

Another post was made while I was typing the below, but decided to still go ahead and make the post in the off chance it should help
-------------------------------------------------
So, presumably for each person they have a Start and an End date that specifies their vacation/holiday period, correct? If you just want to know if they're on vacation for one or more days within a given range, you should be able to the use Between. . .And. . . format but just need to do it twice as in

Select * From tblVacationSchedule Where dtmStartDate Between [Enter Start Date] And [Enter End Date] OR dtmEndDate Between [Enter Start Date] And [Enter End Date];

Something along those lines, right? For a given month, you can either expand it with a little code to translate the month into the first and last day of the month, or if you want a different query it might look something like

Select * From tblVacationSchedule Where Month(dtmStartDate) = [Enter the month (1-12)] Or Month(dtmEndDate) = [Enter the month (1-12)];

Obviously I've made up table and field names for illustrative purposes. If you only want people who were gone the whole time or only gone a part of the time or something else, it may need to be tweaked, but I think that should help point you in the right direction. If not, let us know what else you need!

My earlier post using the datepart function did not take into account if an employee took the whole month off. I have added additional criteria to handle that event. I also put a field in the query that calculates the total number of days taken by each employee for each holiday period. Here is the modified query

SELECT employee, hstart, hend, DateDiff("d",[hstart],[hend])+1 AS holidaytime
FROM holiday
WHERE ((([Enter Month])=DatePart("m",[hstart]) Or ([Enter Month])=DatePart("m",[hend]))) Or (((DateDiff("d",[hstart],[hend])+1)>=28) And (([Enter Month])-1=DatePart("m",[hstart]) Or ([Enter Month]+1)=DatePart("m",[hend])));

Here is the data that I used to make sure that the query was doing what I wanted it to do:

id employee hstart hend
1 joey 5/1/2005 5/4/2005
2 monica 4/29/2005 5/2/2005
3 chandler 5/31/2005 6/5/2005
4 ross 5/6/2005 5/16/2005
5 phoebe 6/6/2005 6/9/2005
6 rachael 4/29/2005 6/1/2005
7 duck 1/1/2005 4/29/2005