I am building a form that runs a query called "Classrooms". This query asks which classroom you want and it returns all corresponding courses for every day (Mon-Sat) in that given classroom.
The problem is, for the control source in the report text boxes, I need an expression to filter out all courses and just display the one I want for the corresponding day and time. Basically, I just need the report to look like a schedule table with the times on the leftmost column and the days of the week across the top.
So, I want to put text boxes to create columns and rows for the schedule and each text box will have to find and display only one course name by running the one query I have, and each text box will only display the course name that corresponds to its position in the schedule report.
I want to find a way so that, for instance, the first class of the week starts at 9AM on Monday, the text box that goes in that position in the schedule runs the query but only displays the class that goes in that position of the schedule. I will then alter the code for the next text box so that it displays only the class that follows which will be on Monday at 10:30AM. The room number will be given by the user at runtime because the query asks for it.
All tables and queries are already created and they work fine.
I know I can create different queries and use one per text box, but I don't want to use different queries for different times of the day and days of the week, because I will then have to create 54 queries.
I wrote the SQL code in another "test" query that does exactly what I need: it retrieves just one course based on a user-given classroom number and a hard-coded day and time. However, I couldn't find any way to link the SQL code to a text box in the report since the only choice to extract data seems to be by building an expression.
So here's the SQL code that does exactly what I need:
SELECT Schedule.COURSECODE
FROM Schedule
WHERE (((Schedule.DAY)="Monday") AND ((Schedule.[START TIME]) Like "*" & "9" & "*") AND ((Schedule.ROOM) Like "*" & [ROOM:] & "*"));
This code asks the user for the room number at runtime and retrieves the class on Monday that starts at 9:00 AM from the Schedule table. However, if using this code in a query I would need to write one query per text box and I rather run just one query that returns all courses, and let an expression in the report extract only the required course that the text box is to display by specifying in the expression the time of the day and the day of the week.
Thanks...
The problem is, for the control source in the report text boxes, I need an expression to filter out all courses and just display the one I want for the corresponding day and time. Basically, I just need the report to look like a schedule table with the times on the leftmost column and the days of the week across the top.
So, I want to put text boxes to create columns and rows for the schedule and each text box will have to find and display only one course name by running the one query I have, and each text box will only display the course name that corresponds to its position in the schedule report.
I want to find a way so that, for instance, the first class of the week starts at 9AM on Monday, the text box that goes in that position in the schedule runs the query but only displays the class that goes in that position of the schedule. I will then alter the code for the next text box so that it displays only the class that follows which will be on Monday at 10:30AM. The room number will be given by the user at runtime because the query asks for it.
All tables and queries are already created and they work fine.
I know I can create different queries and use one per text box, but I don't want to use different queries for different times of the day and days of the week, because I will then have to create 54 queries.
I wrote the SQL code in another "test" query that does exactly what I need: it retrieves just one course based on a user-given classroom number and a hard-coded day and time. However, I couldn't find any way to link the SQL code to a text box in the report since the only choice to extract data seems to be by building an expression.
So here's the SQL code that does exactly what I need:
SELECT Schedule.COURSECODE
FROM Schedule
WHERE (((Schedule.DAY)="Monday") AND ((Schedule.[START TIME]) Like "*" & "9" & "*") AND ((Schedule.ROOM) Like "*" & [ROOM:] & "*"));
This code asks the user for the room number at runtime and retrieves the class on Monday that starts at 9:00 AM from the Schedule table. However, if using this code in a query I would need to write one query per text box and I rather run just one query that returns all courses, and let an expression in the report extract only the required course that the text box is to display by specifying in the expression the time of the day and the day of the week.
Thanks...
