Displaying available instructor using start date and end date

[Guest]

I am trying to display a list of available instructors between two given
dates. I am using the following in my query:

SELECT [Employee Details].[Employee #]
FROM [Employee Details] INNER JOIN EmployeeAvailability ON [Employee
Details].[Employee #]=EmployeeAvailability.[Employee #]
WHERE ((([Employee Details].[Employee #]) And (forms![Activity
Holiday]![Start Date]) Not Between ([Activity Holiday].[start date]) And
([Activity Holiday].[End Date])) And ((Forms![Activity Holiday]![End Date])
Not Between ([Activity Holiday].[Start Date]) And ([Activity Holiday].[End
Date])));

This displays the available instructors but displays them more than once if
they are allocated to an activity within those dates as well as not within
those dates.
e.g. Employee 1 is assigned to activity holiday 1 which runs from 01/05/06
to 05/05/06 as well as activity 4 which runs on 12/05/06 to 16/05/06. As the
second activity does not fall between the dates used to check availability it
display the employee 1 as available when employee 1 is not has it has been
allocated an activity between dates 01/05/06 to 05/05/06. What can I do for
it to not display employee 1 as the employee is not available. Can someone
help please...

 

[OfficeDev18 via AccessMonster.com]

Hi,

I wonder if your SQL statement is a direct copy and paste, because there's an
seeming obvious error in it. I quote:

SELECT [Employee Details].[Employee #]
FROM [Employee Details] INNER JOIN EmployeeAvailability ON [Employee
Details].[Employee #]=EmployeeAvailability.[Employee #]
WHERE ((([Employee Details].[Employee #]) And (forms![Activity
Holiday]![Start Date]) Not Between ([Activity Holiday].[start date]) And
([Activity Holiday].[End Date])) And ((Forms![Activity Holiday]![End Date])
Not Between ([Activity Holiday].[Start Date]) And ([Activity Holiday].[End
Date])));

The phrase

([Employee Details].[Employee #]) And

at the beginning of the WHERE clause seems to be out of place. If you remove
it, you have

SELECT [Employee Details].[Employee #]
FROM [Employee Details] INNER JOIN EmployeeAvailability
ON [Employee Details].[Employee #]=EmployeeAvailability.[Employee #]
WHERE (((forms![Activity Holiday]![Start Date]) Not Between ([Activity
Holiday].[start date]) And
([Activity Holiday].[End Date])) And ((Forms![Activity Holiday]![End Date])
Not Between ([Activity Holiday].[Start Date]) And ([Activity Holiday].[End
Date])));

This looks like it will work.

Sam

I am trying to display a list of available instructors between two given
dates. I am using the following in my query:

SELECT [Employee Details].[Employee #]
FROM [Employee Details] INNER JOIN EmployeeAvailability ON [Employee
Details].[Employee #]=EmployeeAvailability.[Employee #]
WHERE ((([Employee Details].[Employee #]) And (forms![Activity
Holiday]![Start Date]) Not Between ([Activity Holiday].[start date]) And
([Activity Holiday].[End Date])) And ((Forms![Activity Holiday]![End Date])
Not Between ([Activity Holiday].[Start Date]) And ([Activity Holiday].[End
Date])));

This displays the available instructors but displays them more than once if
they are allocated to an activity within those dates as well as not within
those dates.
e.g. Employee 1 is assigned to activity holiday 1 which runs from 01/05/06
to 05/05/06 as well as activity 4 which runs on 12/05/06 to 16/05/06. As the
second activity does not fall between the dates used to check availability it
display the employee 1 as available when employee 1 is not has it has been
allocated an activity between dates 01/05/06 to 05/05/06. What can I do for
it to not display employee 1 as the employee is not available. Can someone
help please...