Run-time error 3061 Too few Parameters. Expected 2

Question

Can someone please let me know what is wrong with this code? I have checked all lines for misspellings - this isnt the issue. All tables and queries are written as they exist in the db. Any help is appreciated.

Private Sub LoadArray()
    '---------------------------
    '---------------------------
    'This procedure loads text into the 3rd column of the array
    '---------------------------
    '---------------------------

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsFiltered As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer

    strSQL = "SELECT tblProperties.Name, tbl1OpportuniyType.Type, qryPropertiesALLTypesALLTbls.TotalUnits, " _
    & "qryPropertiesALLTypesALLTbls.EventStartTimeEachDay, qryPropertiesALLTypesALLTbls.EventEndTimeEachDay, " _
    & "qryPropertiesALLTypesALLTbls.EventStartDate, qryPropertiesALLTypesALLTbls.EventStopDate, " _
    & "qryPropertiesALLTypesALLTbls.TechOpsGroup, qryPropertiesALLTypesALLTbls.TechOpsResource " _
    & "FROM tbl1OpportuniyType RIGHT JOIN (qryPropertiesALLTypesALLTbls INNER JOIN tblProperties ON qryPropertiesALLTypesALLTbls.[PropertyComplex_ID] = tblProperties.[PropertyComplex_ID]) ON tbl1OpportuniyType.[OpportunityType_ID] = tblProperties.OpportunityType " _
    & "WHERE (((qryPropertiesALLTypesALLTbls.EventStartDate) Is Not Null));"



    'Debug.Print strSQL

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

        'This line ensures that the recordset is populated
        If Not rs.BOF And Not rs.EOF Then

        'Loops through the Array using dates for the filter

         For i = LBound(myArray) To UBound(myArray)

        If myArray(i, 1) Then
        'Filters recordset with array dates

         rs.Filter = "[EventStartDate]= " & myArray(i, 0)

        'Open up new recordset based on filter
        Set rsFiltered = rs.OpenRecordset

        'Loop through new recordset
        Do While (Not rsFiltered.EOF)

            'Adds text to the 3rd column of the array
            myArray(i, 2) = myArray(i, 2) & vbNewLine _
            & rsFiltered!Type & " - " & vbNewLine _
            & rsFiltered!Name & " " _
            & rsFiltered!EventStartDate & " - " _
            & rsFiltered!EventStopDate & " " _
            & rsFiltered!EventStartTimeEachDay & " - " _
            & rsFiltered!TechOpsGroup & " " _
            & rsFiltered!TechOpsResource & " " _
            & vbNewLine

        rsFiltered.MoveNext

        Loop


        End If

        Next i


        End If
        rsFiltered.Close
        rs.Close

    'Sets objects to nothing
    Set rsFiltered = Nothing
    Set rs = Nothing
    Set db = Nothing


    End Sub

What error are your receiving? Also, why are you using two recordsets? I'm not certain of what your intended results are, but you should be able to lean out your code by keeping the method down to one recordset. Also... you might try adding rs.movelast and rs.movefirst before your loop. That way your always pointing at the first record in your collection. — Steve W
This article can explain recordsets much better than I could. accessallinone.com/… — Steve W
I'm banking on the error being within your query. Remove the variables in your query and fill them in with dummy data that's in your table(s) and test it. If it works, then begin adding the variables back one by one until you get them all replaced. That way you can effectively isolate where you've gone wrong in your code. In the words of one of my mentors, 90% of a software developer's job is looking for a missing semicolon. — Steve W
Does the query qryPropertiesALLTypesALLTbls reference an open form (forms!someform!somecontrol)? Debug.print strSQL, then copy/paste it into SQL view of the query designer and run it. The source will reveal itself. — AVG
The question about qryPropertiesALLTypesALLTbls was not whether it is necessary. It was whether it contains a reference an open form (forms!someform!somecontrol)? Exactly which line does the error occur on? — AVG

Gustav Gustav · Accepted Answer · 2017-01-24T09:06:06

It isn't clear where myArray comes from, but the filter needs an adjustment to convert the date value to a string expression:

rs.Filter = "[EventStartDate] = #" & Format(myArray(i, 0), "yyyy\/mm\/dd") & "#"

Run-time error 3061 Too few Parameters. Expected 2

1 Answers