display records not on a date range

[ghostman via AccessMonster.com]

is it possible to display all the records not on a specified month?

Ex. How many instructors are available in the month of September 2009?

InstructorID FirstName LastName VacationScheduleF VacationScheduleT
100012 Melchora Aquino 9/01/2009 09/30/2009
100015 Leonor Rivera 11/7/2009 12/7/2009
100021 Maja Salvador 12/15/2009 1/15/2010

the query should display all instructor names except the instructors on
vacation for the month of September 2009.

how can i do that?

Here's the SQL:

SELECT Instructors.[InstructorID], Instructors.[FirstName], Instructors.
[MiddleName], Instructors.[LastName], Instructors.[Initials], Instructors.
[JobPosition], Instructors.[VacationScheduleF], Instructors.
[VacationScheduleT]
FROM Instructors;

i've tried searching here but no luck...please help!
Thanks in advance

 

[Allen Browne]

Try:
SELECT Instructors.*
FROM Instructors
WHERE (Instructors.[VacationScheduleF] < #10/1/2009#)
AND (Instructors.[VacationScheduleT] >= #9/1/2009#);

The holiday includes some September days if it both:
- starts before October 1, and
- ends on/after September 1.

 

[ghostman via AccessMonster.com]

SELECT Instructors.*
FROM Instructors
WHERE (Instructors.[VacationScheduleF] < #10/1/2009#)
AND (Instructors.[VacationScheduleT] >= #9/1/2009#);

The SQL displays the below record when i run the query:

InstructorID FirstName LastName VacationScheduleF
VacationScheduleT
100012 Melchora Aquino 9/01/2009
09/30/2009

This record is the instructor on vacation.

What i want to display is the instructors who are NOT on vacation. (Means
they are available for the month of September 2009)

InstructorID FirstName LastName VacationScheduleF
VacationScheduleT
100015 Leonor Rivera 11/7/2009
12/7/2009
100021 Maja Salvador 12/15/2009
1/15/2010

Also, i would like to make a mini pop up form for the month selection (Jan,
Feb....Dec) using a combo box. How can i connect the selection to my query?

thanks again!

Try:
SELECT Instructors.*
FROM Instructors
WHERE (Instructors.[VacationScheduleF] < #10/1/2009#)
AND (Instructors.[VacationScheduleT] >= #9/1/2009#);

The holiday includes some September days if it both:
- starts before October 1, and
- ends on/after September 1.

is it possible to display all the records not on a specified month?

[quoted text clipped - 21 lines]

i've tried searching here but no luck...please help!
Thanks in advance

 

[Allen Browne]

You want the flip group?

WHERE NOT ((Instructors.[VacationScheduleF] < #10/1/2009#)
AND (Instructors.[VacationScheduleT] >= #9/1/2009#));


--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SELECT Instructors.*
FROM Instructors
WHERE (Instructors.[VacationScheduleF] < #10/1/2009#)
AND (Instructors.[VacationScheduleT] >= #9/1/2009#);

The SQL displays the below record when i run the query:

InstructorID FirstName LastName VacationScheduleF
VacationScheduleT
100012 Melchora Aquino 9/01/2009
09/30/2009

This record is the instructor on vacation.

What i want to display is the instructors who are NOT on vacation. (Means
they are available for the month of September 2009)

InstructorID FirstName LastName VacationScheduleF
VacationScheduleT
100015 Leonor Rivera 11/7/2009
12/7/2009
100021 Maja Salvador 12/15/2009
1/15/2010

Also, i would like to make a mini pop up form for the month selection
(Jan,
Feb....Dec) using a combo box. How can i connect the selection to my
query?

thanks again!

Try:
SELECT Instructors.*
FROM Instructors
WHERE (Instructors.[VacationScheduleF] < #10/1/2009#)
AND (Instructors.[VacationScheduleT] >= #9/1/2009#);

The holiday includes some September days if it both:
- starts before October 1, and
- ends on/after September 1.

is it possible to display all the records not on a specified month?

[quoted text clipped - 21 lines]

i've tried searching here but no luck...please help!
Thanks in advance

 

[ghostman via AccessMonster.com]

thanks!

how about using a combo box to let the user select the month?
how can i do this?

You want the flip group?

WHERE NOT ((Instructors.[VacationScheduleF] < #10/1/2009#)
AND (Instructors.[VacationScheduleT] >= #9/1/2009#));

SELECT Instructors.*
FROM Instructors

[quoted text clipped - 42 lines]

 

[Allen Browne]

Assuming the combo returns a value between 1 and 12, and you want the
current year, replace #9/1/2009# with an expression like this:
DateSerial(Year(Date()), Nz([Forms].[Form1].[Combo1], Month(Date())), 1)

Use +1 just before the last comma for the #10/1/2009#.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks!

how about using a combo box to let the user select the month?
how can i do this?

You want the flip group?

WHERE NOT ((Instructors.[VacationScheduleF] < #10/1/2009#)
AND (Instructors.[VacationScheduleT] >= #9/1/2009#));

SELECT Instructors.*
FROM Instructors

[quoted text clipped - 42 lines]

i've tried searching here but no luck...please help!
Thanks in advance

 

[John Spencer]

Simplest query would be

SELECT *
FROM INSTRUCTORS
WHERE InstructorID NOT IN
(SELECT Instructors.InstructorID
FROM Instructors
WHERE Instructors.[VacationScheduleF] < #10/1/2009#
AND Instructors.[VacationScheduleT] >= #9/1/2009#)

Or
SELECT *
FROM INSTRUCTORS
WHERE NOT EXISTS
(SELECT *
FROM Instructors as TEMP
WHERE TEMP.[VacationScheduleF] < #10/1/2009#
AND TEMP.[VacationScheduleT] >= #9/1/2009#
AND Temp.InstructorID = Instructors.InstructorID)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[ghostman via AccessMonster.com]

The SQL displays all available instructors except those instructors who are
on vacation during the period of 9/1/2009 to 10/1/2009. (as mentioned in the
criteria)

SELECT Instructors.*
FROM Instructors
WHERE (((Instructors.VacationScheduleF)>=#10/1/2009#) AND ((Instructors.
VacationScheduleT)>=#9/1/2009#));

i want to modify this query to display records based on user input using 2
textboxes.
Like:

Start Date: (Date)
End Date: (Date)

and this will go to my report based on the above query.

What i have is a pop up form [frmInstructorsAvailable] with two textboxes
(txtStartDate & txtEndDate) and a command button [cmdPreview] to trigger the
event.

please help...

Assuming the combo returns a value between 1 and 12, and you want the
current year, replace #9/1/2009# with an expression like this:
DateSerial(Year(Date()), Nz([Forms].[Form1].[Combo1], Month(Date())), 1)

Use +1 just before the last comma for the #10/1/2009#.

thanks!

[quoted text clipped - 11 lines]

 

[Allen Browne]

In place of:
#10/1/2009#
use a reference to your text box, such as:
[Forms].[Form1].[Start Date]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

The SQL displays all available instructors except those instructors who
are
on vacation during the period of 9/1/2009 to 10/1/2009. (as mentioned in
the
criteria)

SELECT Instructors.*
FROM Instructors
WHERE (((Instructors.VacationScheduleF)>=#10/1/2009#) AND ((Instructors.
VacationScheduleT)>=#9/1/2009#));

i want to modify this query to display records based on user input using 2
textboxes.
Like:

Start Date: (Date)
End Date: (Date)

and this will go to my report based on the above query.

What i have is a pop up form [frmInstructorsAvailable] with two textboxes
(txtStartDate & txtEndDate) and a command button [cmdPreview] to trigger
the
event.

please help...

Assuming the combo returns a value between 1 and 12, and you want the
current year, replace #9/1/2009# with an expression like this:
DateSerial(Year(Date()), Nz([Forms].[Form1].[Combo1], Month(Date())), 1)

Use +1 just before the last comma for the #10/1/2009#.

thanks!

[quoted text clipped - 11 lines]

i've tried searching here but no luck...please help!
Thanks in advance

 

[ghostman via AccessMonster.com]

thanks for the response!
but one more problem...

when i enter a date range:
Start Date: 11/01/2009
End Date: 12/09/2009

the query displays all instructor's vacation schedule including the
instructor who will be on vacation from 11/07/2009 to 12/07/2009 while the
instructor is on vacation within that period. It should not display that
record since the vacation date is covered on that date.

SQL:

SELECT Instructors.*
FROM Instructors
WHERE NOT (((Instructors.VacationScheduleF)=Forms.[frmIntructor Available].
txtStartDate) And ((Instructors.VacationScheduleT)=Forms.[frmIntructor
Available].txtEndDate));

Thank you..

In place of:
#10/1/2009#
use a reference to your text box, such as:
[Forms].[Form1].[Start Date]

The SQL displays all available instructors except those instructors who
are

[quoted text clipped - 34 lines]

 

[Allen Browne]

Now you have that working, go back to the first response I gave you.

Look at the WHERE clause in the example. You will see it is quite different
from the one you are using.

Follow the example.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks for the response!
but one more problem...

when i enter a date range:
Start Date: 11/01/2009
End Date: 12/09/2009

the query displays all instructor's vacation schedule including the
instructor who will be on vacation from 11/07/2009 to 12/07/2009 while the
instructor is on vacation within that period. It should not display that
record since the vacation date is covered on that date.

SQL:

SELECT Instructors.*
FROM Instructors
WHERE NOT (((Instructors.VacationScheduleF)=Forms.[frmIntructor
Available].
txtStartDate) And ((Instructors.VacationScheduleT)=Forms.[frmIntructor
Available].txtEndDate));

Thank you..

In place of:
#10/1/2009#
use a reference to your text box, such as:
[Forms].[Form1].[Start Date]

The SQL displays all available instructors except those instructors who
are

[quoted text clipped - 34 lines]

i've tried searching here but no luck...please help!
Thanks in advance