Sql & vba

[grundig1987]

I am trying to set up an SQL statement based on some VBA:

I have set up an access form which has various combo boxes and text boxes - I have set up some VBA to record certain aspects of the data, which is below:

StudentCourse = Combo_Show_Students.Value

Creditsoncourse = DLookup("[CourseCredits]", "Courses", "[CourseID] = '" & Combo11.Value & "'")

SemesterCourse = DLookup("[COurseSemester]", "Courses", "[CourseID] = '" & Combo11.Value & "'")

So for example, the StudentCourse could be B787878, the CreditsonCourse could be 20, and SemesterCourse could be 1.

I have set up another list box which I want to run off a query, the query being show StudentID from 'EnrolmentsSecondary' Where StudentID = StudentCourse, CourseSemester = SemesterCourse, and CourseCredits = CreditsonCourse.

The SQL I have is below:

SELECT EnrolmentsSecondary.CourseID
FROM EnrolmentsSecondary, Courses
WHERE (((EnrolmentsSecondary.StudentID)='" & StudentCourse & "';") AND ((Courses.CourseSemester)='" & SemesterCourse & "';") AND ((Courses.CourseCredits)='" & CreditsonCourse & "';"))

This does not appear to be working correctly - Any ideas what I am doing wrong?

Thanks,

Mark

 

[jdraw]

Mark,

Your SQL "EnrolmentsSecondary.CourseID" is only Selecting this field (CourseID). From a syntax view this field is in the EnrolmentsSecondary Table. You don't need the Courses Table, nor any of the
"AND ((Courses.CourseSemester)='" & SemesterCourse & "';") AND ((Courses.CourseCredits)='" & CreditsonCourse & "';"))" info related to Courses.

UNLESS your tables are totally unnormalized etc.

It might be better if you showed us your table structures, along with the keys and any relationships.

 

[boblarson]

And I'll also note that you don't put a semi colon (;) in multiple times. If you use it, it comes at the very end of the SQL statement - last thing. But you don't even need it in Access, really.

 

[grundig1987]

Mark,

Your SQL "EnrolmentsSecondary.CourseID" is only Selecting this field (CourseID). From a syntax view this field is in the EnrolmentsSecondary Table. You don't need the Courses Table, nor any of the
"AND ((Courses.CourseSemester)='" & SemesterCourse & "';") AND ((Courses.CourseCredits)='" & CreditsonCourse & "';"))" info related to Courses.

UNLESS your tables are totally unnormalized etc.

It might be better if you showed us your table structures, along with the keys and any relationships.

Jdraw - Thanks for the reply - I think the two tables you would be interested in are 1) EnrolmentsSecondary and 2) Courses.

EnrolmentsSecondary has two fields which both combine to create the primary key for this table; they are, StudentID and CourseID.

In the courses Table, the Primary Key is the CourseID, and the other fields are CourseName, CourseSemester, CourseCredits, MaxStudents, MinStudents.

Thanks,

Mark