Filter and order form data by data in related table???

[BallunarCrew]

I have two tables - Patients and Appointments. It is a one-to-many relationship between the two. The common key is PTID - a string field. I have a form based on the Patients table with a sub-form that shows all the appointments for a patient. I have a combo box that allows the Dr to filter the patients form based on the appointment date. It uses the following code to set the filter:

Me.Filter = "[PTID ] in (Select PTID from TodaysPatients)"

TodaysPatients is a query (see below) that gets the PTIDs from the Appintments table where the appointment date is the current date.

I have a requirement to order the filtered data based on the appointment time. There is a field in the appointments table ApptDate, that contains the appointment date and time formatted as: 1/10/2011 11:00:00 AM (it is used to create an Outlook appointment). If I run the query by itself, it sorts just fine but the Filter statement above will not sort. This has got to be due to the fact that I am just getting the PTIDs that are returned by the query - no connection to the order of the data in the query. I tried adding an order by statement to the filter and it does not help.

Here is the TodaysPatients query SQL code:
SELECT PTID, DateToCompare, ApptDate, lName, fName
FROM Appointments
WHERE DateToCompare =Date
ORDER BY ApptDate, lName, fName;

Any ideas or feedback would be greatly appreciated.