Access query with criteria in pre-query 1

[VBAPrincess]

I have a calendar which displays classes scheduled on each day of a given month. When I click on a day, I have a list box below the calendar that displays more details about each class scheduled on the selected day. I have 15 classrooms and I'd like to see all classrooms in the listbox, and if there is a class scheduled to be in that room, I wish to see the name, start date, end date (because some run 2+ days), instructor last name, and total class enrollment.

I've created the query but am not getting all classrooms to appear. For example, July 19 had 6 classes running, so there should be 9 blank rows with only the room number. I am only getting 4 blank room rows. I figured out I need to do the filter on the Schedule table before doing the query with the outer join to classrooms.

The SQL statement is being created in a sub procedure and then the rowsource of the listbox is set to the SQL statement.

Here's the query that works properly:
SELECT tblClassrooms.RoomNum, qsubCalEventsA.StartDate, qsubCalEventsA.EndDate, qsubCalEventsA.InstLName, qsubCalEventsA.RoomID
FROM tblClassrooms LEFT JOIN qsubCalEventsA ON tblClassrooms.RoomID = qsubCalEventsA.RoomID
ORDER BY tblClassrooms.RoomNum;

The problem is that the criteria is in the query "qsubCalEventsA" and the criteria will change each time a user clicks a day on the calendar.

So, my question is ... what is the best approach? I need the filter to happen before joining the results with the classrooms table.

I haven't used subqueries much so I don't know if that is an option. I have used code before to modify a QueryDef but thought I'd get some input first.

Thanks in advance!


Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[PHV]

What is the SQL code of qsubCalEventsA ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VBAPrincess]

PHV,

Here's the SQL for qsubCalEventsA:

SELECT tblSchedule.ClassID, tblClasses.ClassNum, tblClasses.ClassName, tblSchedule.StartDate, tblSchedule.EndDate, tblInstructors.InstLName, tblSchedule.RoomID
FROM tblClasses RIGHT JOIN (tblInstructors RIGHT JOIN tblSchedule ON tblInstructors.InstructorID = tblSchedule.InstructorID) ON tblClasses.ClassDescID = tblSchedule.ClassDescID
WHERE (((40743) Between [tblSchedule].[StartDate] And [tblSchedule].[EndDate]));


Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[PHV]

What about this ?

SELECT R.RoomNum, S.StartDate, S.EndDate, I.InstLName, S.RoomID
FROM (tblClassrooms R
LEFT JOIN (
  SELECT ClassID, StartDate, EndDate, RoomID, InstructorID FROM tblSchedule
  WHERE #2011-07-19# Between StartDate And EndDate
) S ON R.RoomID = S.RoomID)
LEFT JOIN Instructors I ON S.InstructorID = I.InstructorID
ORDER BY R.RoomNum

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[VBAPrincess]

Thanks PHV! That worked and I'll be able to still use a SQL statement behind the calendar to build the record source for the list box.

Much appreciated!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!