Search between a range of dates from two date fields

[danielw2007]

I found some information fro a previous thread on here but needed add onto it.

The thread in question is "Search between two date fields" by Ross Burrows.

I have a Annual Leave Database that im working on and it asks for a Start Date and a End date. (First day off - last day off.

I am trying to write a query that generates a report so it tell me everyone that's off in any given time, IE next monday to friday. so you enter the first date (Monday) and then the next date (Friday) and it tell you everyone thats off in that given time.

So what i've got to work with is a

StarDate - Staff's First day of Annual Leave
EndDate - Staff's Last day of Annual Leave
&
FirstDate - Monday's Date
LastDate - Friday's Date

My problem i have got is how do i get it to check each date in the week to find if someones off on that day?

If you require any mroe information on what i am on about please feel free to ask.

 

[MSAccessRookie]

I believe that a query silimar to the following will give you what you want:

SELECT Date, PersonName,...{ and any other relevant information }
FROM { Tables JOINED BY Appropriate Key Columns }

WHERE ( ( DATE BETWEEN FirstDate AND LastDate ) AND ( DATE BETWEEN StarDate AND EndDate )

Order by Date, Person { , any other order criteria }

 

[danielw2007]

Date Between

Hi MSAccessRookie,

The Where statement is coming up with an error.

Doe's the "DATE" in "Date between" have any purpose?


​

 

[MSAccessRookie]

DATE is a reserved command word in Access

It occurs to me that DATE is a reserved command word in Access. Try changing the Column name to Something else (Like DateToCheck) and it should work properly. You can also try enclosing the word Date between Brackets (Like [Date]), but I do not believe that this is a preferred method.

The Date Between in the example was an attempt to use the BETWEEN command that SQL provides to compare a data value to an entire range of values as opposed to each value contained in the range

 

[Jon K]

My problem i have got is how do i get it to check each date in the week to find if someones off on that day?

To list who will be off each day next Monday to Friday, you will need to have one query for each day and combine them in a master query of employee list with Left Joins.

See the query "List Next Week Day By Day" in the attached database. You can see how the Left Joins are created by double-clicking on one of the table/query joining lines in query Design View.

When the query is run anytime this week June 8 - 14, it should return the results for next week:

[b]
EmployeeID FirstName LastName	Monday	Tuesday	Wednesday Thursday Friday[/b]
001	   Val	     Kilmer					
002	   Kim	     Basinger					
003	   Russell   Mulcahy		Off	Off	  Off	
004	   Karen     McCoy	Off	Off	Off	  Off	   Off
005				Off	Off	Off	  Off	
006				Off			  Off	   Off
007							
008

In the database, I have also included a query to search the two date fields for a range of dates (next Monday - Friday) for comparison with the above query:-

SELECT EmployeeID, StarDate, EndDate
FROM tblLeave
WHERE EndDate >= Date()-DatePart("w",Date())+9 And StarDate <= Date()-DatePart("w",Date())+13;

Here Date()-DatePart("w",Date())+9 is next Monday
and Date()-DatePart("w",Date())+13 is next Friday.
.

 

[danielw2007]

Hey Jon That looks brilliant

Hey thanks Jon that looks brilliant,

I have done a little changing of the details to match this week, and is working great now thanks.

Also i have enter in the criteria "Off" so it doesn't display peole that aren't on annual leave.

Thanks for your help.

 

[billwest]

What parameter is being used in this script to make it work including the weekend. If I try adding saterday to the query it does not work correctly.

 

[Dream.Van]

I"m looking for something really simillar but with numbers instead of dates, can it be done?

I have maps that are divided in ranges and lots (Like a grid). I have 2 lot field (number fields), one is the begining of the interval and the other is the end (because maps cover more than one lot). I want to search a lot in that interval.

Example: I want all the maps that show lots 7 to 9 and it gives me the perfect matches, the ones that have a larger range (like 2 to 10) and those who only have part of the criterias (like 5 to 8)


Hope I'm clear enough
Thank's for helping