CODEWITHSUNDEEP
ms-accessvb6
user2880002
user2880002

Reputation: 11

No Value for one or more required parameters

I have this SQL query

cmd.CommandText = "SELECT [tblStudent]![LastName] & ', ' & [tblStudent]![FirstName] & ' ' & [tblStudent]![MiddleName] AS FullName," & _
" tblGrade.FirstGrading, tblGrade.SecondGrading, tblGrade.ThirdGrading, tblGrade.FourthGrading," & _
" Avg(b.GradeValue) AS AverageGrade" & _
" FROM ((((tblGrade INNER JOIN tblEnrolment ON tblGrade.EnrolmentID=tblEnrolment.EnrolmentID)" & _
    " INNER JOIN tblStudent ON tblStudent.StudentID=tblEnrolment.StudentID)" & _
    " INNER JOIN tblSubjectOffering ON tblSubjectOffering.SubjectOfferingID=tblGrade.SubjectOfferingID)" & _
    " INNER JOIN tblSubject ON tblSubject.SubjectID=tblSubjectOffering.SubjectID)" & _
    " INNER JOIN tblSection ON tblSection.SectionID=tblSubjectOffering.SectionID" & _
" WHERE tblSection.SectionTitle='" & s(0) & "' AND tblSubject.SubjectID='" + s2(0) + "'" & _
" GROUP BY [tblStudent]![LastName] & ', ' & [tblStudent]![FirstName] & ' ' & [tblStudent]![MiddleName], tblGrade.FirstGrading, tblGrade.SecondGrading, tblGrade.ThirdGrading, tblGrade.FourthGrading" & _
" ORDER BY [tblStudent]![LastName] & ', ' & [tblStudent]![FirstName] & ' ' & [tblStudent]![MiddleName]"

And this code gives me a runtime error "No value for one or more required parameters".

Upvotes: 0

Views: 118

Answers (2)

techturtle
techturtle

Reputation: 2587

Very difficult to offer suggestions with only a SQL statement, out of context with your other code and objects.

As it stands, I see two main potential problems. First is that you have Avg(b.GradeValue) but there is no reference to the b table elsewhere in the command. For this, you would need to remove the b or make sure the reference is valid.

Second, you have two variables, s() and s2() that could be returning invalid or null data. You may want to debug this step and check the values each of these holds before letting it proceed, then fix the problem in the other part of your code if one is bad.

Upvotes: 1

HansUp
HansUp

Reputation: 97100

My hunch is Access thinks b.GradeValue is a parameter because there is no table name or alias for b:

Avg(b.GradeValue) AS AverageGrade

I suggest you Debug.Print the completed SELECT statement, or write it to a text file, then test it as a new query in the Access query designer. When it asks you to supply the parameter value, it also shows you the "name" of whatever it thinks is the parameter.

Upvotes: 2

Related Questions