Help on query

[gear]

I need help on my query. I have made an employees leave plan database. This has two text fields [StartDate] and [EndDate]. I want search by month/year (like Jan 2009, Feb 2009 etc) and my search should give me a list of employees on leave during the current month (or future month). This will facilitate making alternative work arrangements.

My efforts to make a query did not succeed.

There is one more issue to be sorted out. Suppose Mr.A is on leave from 16 Jan 2009 to 15 Mar 2009, if I search for Feb 2009, Mr.A should appear in the list because he is on leave during the entire month. I failed to find a solution. Please help

 

[Kafrin]

So you choose a month. Set it up so that this automatically returns the first day of that month.

Now you want to build your query with the criteria that:

StartDate < DateAdd("m", 1, ChosenDate) AND EndDate >= ChosenDate

So you find people who's start date is before the end of the month (actually strictly before the first of the next month) and who's end date is after or equal to the start of the month.

 

[gear]

Thanks for your reply. This is what I understand from your reply.
1. I need to add two fields (say StartMonth & EndMonth) to my table wherein the first day of the month and year will be inserted after filling up [StartDate] and [EndDate].
2. I need to use these two fields in the search criteria.
Please guide me if I am wrong.

 

[Dennisk]

Don't create new fields, use the Access functions Month(YourDate) and Year(YourDate) to return the month/year part of a date. You use these functions in your query.

 

[gear]

Sorry for late reply, Mr. Kafrin
At first I could not grasp the underlying concept in what you have written. Hence I got confused.
I thought over it again and now it is clear. I have implemented it and it is working perfectly.
Thanks once again.

 

[Kafrin]

Excellent, glad you got it to work