OpenRecordset is not applying query parameters

Question

I'm trying to iterate through the result set generated with the query that has 2 parameters. Values for these parameters are read from the form fields (start and end date). Since Access throws Run-time error '3061'. Too few parameters. Expected 2. even if the value is set in the form fields, I tried to set the parameters through VBA with QueryDef object (given code below).

It worked OK when start and end date are the same, but if I select different start and end date it won't apply the date filter assigned to the query parameters.

I've tried both to change format of the date values and to cast them to another type, but I've had no success.

Has anyone experienced a similar problem? Any help would be appreciated!

Query:

SELECT DISTINCT 
    tblComp_Payout.Agent_ID_int As [Agent ID],
    tblExchOffices.Agent_Name AS Name
FROM
    tblExchOffices 
INNER JOIN 
    tblComp_Payout ON tblExchOffices.Agent_ID_int = tblComp_Payout.Agent_ID_int
WHERE 
    ((DateValue([Paid_Date])) >= ([forms]![frmReporting]![txtDateFrom])
    AND (DateValue([Paid_Date]))<=[forms]![frmReporting]![txtDateTo])

UNION 

SELECT DISTINCT 
    tblComp_Sending.Agent_ID_int AS [Agent ID],
    tblExchOffices.Agent_Name AS Name
FROM
    tblExchOffices 
INNER JOIN 
    tblComp_Sending ON tblExchOffices.Agent_ID_int = tblComp_Sending.Agent_ID_int
WHERE 
    ((DateValue([Sending_Date])) >= ([forms]![frmReporting]![txtDateFrom])
    AND (DateValue([Sending_Date]))<=[forms]![frmReporting]![txtDateTo]);

Method:

Private Sub iterate_Click()
On Error GoTo iterate_Err
    
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("queAgentByDate")
    qdf.Parameters.Refresh
    
    If CurrentProject.AllForms("frmReporting").IsLoaded Then
        qdf.Parameters("[forms]![frmReporting]![txtDateFrom]") = CStr([Forms]![frmReporting]![txtDateFrom])
        qdf.Parameters("[forms]![frmReporting]![txtDateTo]") = CStr([Forms]![frmReporting]![txtDateTo])
    Else
        Beep
        Resume iterate_Exit
    End If
    
    Set rs = qdf.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    
    MsgBox rs.RecordCount
    
    If rs.EOF Then Exit Sub

    With rs
        Do Until .EOF
           'Loop logic
        Loop
    End With
 

    rs.Close
    Set rs = Nothing

iterate_Exit:
    Exit Sub

iterate_Err:
    MsgBox Error$
    Resume iterate_Exit
End Sub

That's not how you define query parameters ([forms]![frmReporting]![txtDateFrom]). Read a bit about query parameters - define them in your query as dates, then use the parameter names in your query and code — dbmitch, Aug 25 '21 at 00:45
Review https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access — June7, Aug 25 '21 at 04:18

score 0 · Accepted Answer · answered Aug 25 '21 at 08:48

First, specify your parameters:

PARAMETERS
    [forms]![frmReporting]![txtDateFrom] DateTime,
    [forms]![frmReporting]![txtDateTo] DateTime;
SELECT DISTINCT tblComp_Payout.Agent_ID_int As [Agent ID],
        tblExchOffices.Agent_Name AS Name
FROM tblExchOffices INNER JOIN tblComp_Payout 
    ON tblExchOffices.Agent_ID_int = tblComp_Payout.Agent_ID_int
WHERE ((DateValue([Paid_Date]))>=([forms]![frmReporting]![txtDateFrom])
        And (DateValue([Paid_Date]))<=[forms]![frmReporting]![txtDateTo])
UNION 
SELECT DISTINCT tblComp_Sending.Agent_ID_int As [Agent ID],
        tblExchOffices.Agent_Name AS Name
FROM tblExchOffices INNER JOIN tblComp_Sending 
    ON tblExchOffices.Agent_ID_int = tblComp_Sending.Agent_ID_int
WHERE ((DateValue([Sending_Date]))>=([forms]![frmReporting]![txtDateFrom])
        And (DateValue([Sending_Date]))<=[forms]![frmReporting]![txtDateTo]);

Then set their values as true date values:

qdf.Parameters("[forms]![frmReporting]![txtDateFrom]") = [Forms]![frmReporting]![txtDateFrom]
qdf.Parameters("[forms]![frmReporting]![txtDateTo]") = [Forms]![frmReporting]![txtDateTo]

To simplify, rename your parameters to, say, DateFrom and DateTo.

Thank you very much! This makes sense. Didn't figure I had to declare parameters in the query as well. — npdrums, Aug 25 '21 at 18:13

OpenRecordset is not applying query parameters

1 Answers1