Query criteria

[Daryl2106]

Hi,

I have a query that shows the classes that students are enrolled in. The results are displayed in a subform linked to the students ID#. The enrollment records (classes students are enrolled in) are identified by a four digit "ClassNumber" that is linked to a course name, section, instructor and year code. The year codes are 1129 for 2012-2013 and 1139 for 2013-2014. Students may have taken a course in a previous year (e.g 1129)and completed it. This is recorded as 100 (in a percent complete field).

When I run the query all enrollment records are returned correctly but the ClassNumber field for the completed courses are vacant. The ClassNumbers for the "active" courses are showing.

This is okay, except I would like some way of displaying the word "complete" or "2012-2013" or something in that empty field. Can this be done?




Here is the SQL:

SELECT StudentEnrollmentTable.StudentNumber, StudentEnrollmentTable.AnnualClassNumberID, AnnualClassNumbers1.FKCourseInventoryID, AnnualClassNumbers1.ClassNumber, AnnualClassNumbers1.TermNumber, CoursesNEW.[Course Title], SectionNEW.[Class Section], StudentEnrollmentTable.CourseStatus, StudentEnrollmentTable.StartDate, StudentEnrollmentTable.EndDate, StudentEnrollmentTable.Progress, StudentEnrollmentTable.GradeToDate, StudentEnrollmentTable.CourseCompletionDate, [LU_Instructor]![Initials] & " " & [LU_Instructor]![LastName] AS InstructorID
FROM SectionNEW INNER JOIN (CoursesNEW INNER JOIN ((LU_Instructor INNER JOIN AnnualClassNumbers1 ON LU_Instructor.InstructorID = AnnualClassNumbers1.[FKInstructorID]) INNER JOIN StudentEnrollmentTable ON AnnualClassNumbers1.ID = StudentEnrollmentTable.AnnualClassNumberID) ON CoursesNEW.PKCourseID = AnnualClassNumbers1.FKCourseInventoryID) ON SectionNEW.PKSectionID = AnnualClassNumbers1.FKSectionID
WHERE (((StudentEnrollmentTable.StudentNumber)=[Forms]![ALPc Student/Course Enrollment Form]![StudentNumber]) AND ((AnnualClassNumbers1.TermNumber)=1129)) OR (((AnnualClassNumbers1.TermNumber)=1139));

Thanks and take care,

Daryl

[June7]

Maybe:

Nz(AnnualClassNumbers1.ClassNumber, "Completed") AS ClassNum

[Daryl2106]

Thanks June!

[Daryl2106]

Hi June7,

I am still working on a couple of things. The code above; Where does it go? Is it include in the SQL or in a criteria field on a query?


TC,

Daryl

[June7]

It goes in the SELECT clause of query. It replaces the ClassNumber field.

Alternatively, can use expression in textbox:

=Nz([ClassNumber], "Completed")

[Daryl2106]

That for getting back to me on this one June7. I tried entering it in the SQL and it made no difference. (I did however start to see how SQL is organized). When you say "use expression in text box"... I am not sure... Currently the field is display in a ComboBox from which our Admin Assist can select ClassNumbers from when student enroll in a new course or change sections, etc. Do you suggest changing this to a text box or creating one on the form to diplay when a course is completed.

Thanks and TC,

Daryl

[June7]

What do you mean it made no difference - the field still shows empty in the query? If you want to provide db for analysis, follow instructions at bottom of my post. Identify objects involved in issue.

Not suggesting you change combobox.