CODEWITHSUNDEEP
ms-accessvba
user1804254
user1804254

Reputation: 59

Error 3061 Too few parameters 2 expected

I have been facing the error 3061 with error message "Too few Parameters: Expected 2". I have done all of the following to resolve the issue but still couldn't it.

  1. I ran the query in SQL mode and it gives me result
  2. I checked all the field names
  3. I checked all the "&" s are placed. I find them correct.

Here is my code:

Private Sub cmbYear_Change()

    Dim db As Database
    Dim rs As DAO.Recordset
    Dim Query As String

    Query = " SELECT Yrs_Teaching, Highest_Edu, AD_Descr FROM ClassSurvey" & _
            " WHERE ClassSurvey.Program/School_ID = " & Me.cmbProgId.Value & _
            " AND ClassSurvey.ClassID = " & Me.cmbClassId.Value & _
            " AND ClassSurvey.Teacher_ID = " & Me.cmbTeacherID.Value & _
            " AND ClassSurvey.SYear = " & Me.cmbYear.Value


    Set db = CurrentDb
    Set rs = db.OpenRecordset(Query)

    If rs.RecordCount > 0 Then
        Me.TB1 = rs!Yrs_Teaching
        Me.TB2 = rs!Highest_Edu
        Me.TB3 = rs!AD_Descr

    Else
        Me.TB1 = "N/A"
    End If

    Set rs = Nothing
    Set db = Nothing
End Sub

Upvotes: 1

Views: 30553

Answers (4)

Jake Allen
Jake Allen

Reputation: 1

I had this issue too, I realized it was because I did not put quotes around my variables.

This was fixed by adding '& Chr(34)' around my variables

My fixed code looks like:

TextProducer = [Forms]![MyFormName]![TextInputBoxName]
strQuery = "SELECT FILEMASK" & _
" FROM TABLE_NAME" & _
" WHERE Producer = " & Chr(34) & TextProducer & Chr(34)

Upvotes: 0

Joseph Mathew
Joseph Mathew

Reputation: 1

This error may be because the column names in the query have special characters. Try surrounding the column names with square brackets in the SQL query. Column name with special symbols should be within square brackets and variables should be inside single quotes.

Upvotes: 0

Avagut
Avagut

Reputation: 994

I came across this when I was looking for a solution to the same problem. Turns out one of the values from a control on the form was not passing the value to the statement, sending it to the debug window (Debug.print) helped me spot the problem after a long time because I was using a global variable which the sql query was parsing. So load your controls' values into variables first!

Upvotes: 0

HansUp
HansUp

Reputation: 97101

It appears your table includes a field named Program/School_ID. Bracket that field name in the SELECT statement so the db engine can properly recognize it as one field name.

That change might be all you need. But if you have another problem, give yourself an opportunity to examine the completed SELECT statement you're giving to the db engine. It might not be what you expect.

Dim db As Database
Dim rs As DAO.Recordset
Dim strQuery As String

strQuery = "SELECT cs.Yrs_Teaching, cs.Highest_Edu, cs.AD_Descr FROM ClassSurvey AS cs" & _
        " WHERE cs.[Program/School_ID] = " & Me.cmbProgId.Value & _
        " AND cs.ClassID = " & Me.cmbClassId.Value & _
        " AND cs.Teacher_ID = " & Me.cmbTeacherID.Value & _
        " AND cs.SYear = " & Me.cmbYear.Value

Debug.Print strQuery 
Set db = CurrentDb
Set rs = db.OpenRecordset(strQuery)

If you get an error, you can go to the Immediate window (Ctrl+g), copy the statement text from there, open a new query in the query designer, switch to SQL View, paste in the statement text and try running it there. This tip is especially useful when the db engine complains about a missing parameter because when you try to run the query from the designer, Access will show you an input box asking you to supply a value and that box also contains the name of whatever Access thinks is the parameter.

Upvotes: 9

Related Questions