Two things I saw in the first post:
but where I get into trouble is I want to add this at the end of the where clause and then an order statement and I just can't get it
"WHERE StudentCourse.CourseStatus In ('Registered','In Progress','Completed','Failed Exam','Failed Course')" & _
ORDER BY Student.LastName, Student.FirstNames;<snip>
1)There can be only 1 WHERE clause in a SQL statement
2) It looks like there was a missing space before the "ORDER BY" clause.
In code I like to break up the SQL into multiple lines concatenated together; it is easier to read. I add a space at the beginning of each line instead of at the end of the line. For me, it is easier to see when I am missing a space.
Here is the code
Code:
sSQL = "SELECT Exam.SerialID, Student.ProperLastName, Student.ProperFirstName, "
sSQL = sSQL & " IIf(IsNull([Student]![ProperLastName]),StrConv([Student]![LastName],3),[Student]![ProperLastName]) AS LName,"
sSQL = sSQL & " IIf(IsNull([Student]![ProperFirstName]),StrConv([Student]![FirstNames],3),[Student]![ProperFirstName]) AS FName,"
sSQL = sSQL & " Student.DateofBirth, Student.Address, Student.PostalCode, Student.City,"
sSQL = sSQL & " Student.Prov, 'Yes' AS [40 hour course completed], Exam.ExamID, Exam.Attempt,"
sSQL = sSQL & " IIf([Student].[Prov Exam Language] = '2', 'French','') AS [Language],"
sSQL = sSQL & " Exam.ExamDate, Exam.ExamTime"
sSQL = sSQL & " FROM Student INNER JOIN (Instructors INNER JOIN Exam ON Instructors.InstructorID = Exam.InstructorID)"
sSQL = sSQL & " ON Student.StudentID = Exam.StudentID"
sSQL = sSQL & " WHERE Exam.SerialID = " & Me.SerialID
sSQL = sSQL & " And StudentCourse.CourseStatus In ('Registered','In Progress','Completed','Failed Exam','Failed Course')"
sSQL = sSQL & " ORDER BY Student.LastName, Student.FirstNames;"
Debug.Print sSQL
Note that I use sSQL because SQL is a reserved word. "Name" is also a reserved word. Reserved words shouldn't be used as object names.
The line "Debug.Print sSQL" will print the SQL to the immediate window.
Copy the SQL in the immediate window and paste it into a query, then try and to execute it.
If the query won't execute, you can fix the query. Once the query executes, modify the code.