We help IT Professionals succeed at work.

Problem passing Parameters using DAO QueryDef

I am trying to pass a start and end date to a parameter query using DAO querydef. Even though I assume I have passed the parameters in the following code, I am still being prompted to enter the dates. What am I missing?
'qryRpt_ByRelease
SELECT FROM tblProjectControl " _
& "tblProjectControl.TargetDT, " _
& "tblProjectControl.SomeField2, " _
& "tblProjectControl.SomeField3 " _ 
...
& "WHERE tblProjectControl.targetID " _
& ">=[StartDT] And <= [EndDT];"


Private Sub cmdBuildReleaseReport_Click()
'On Error GoTo Err_cmdBuildReleaseReport_Click
    
    Dim StartDT As Date
    Dim EndDT As Date
    StartDT = cboReleaseMonth.Column(2)
    EndDT = cboReleaseMonth.Column(3)

    Dim DB As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Set DB = CurrentDb
    Set qdf = DB.QueryDefs("qryRpt_ByRelease")
    
    qdf.Parameters("StartDT") = StartDT
    qdf.Parameters("EndDT") = EndDT
    Set rs = qdf.OpenRecordset()
    With rs
        DoCmd.OpenQuery "qryRpt_ByRelease"
        .Close
    End With

End Sub

Open in new window

Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
This should explain it:

Courtesy of EE's mbizup:

"References to form controls work fine in stored queries.  If you take that same query and open it through VBA, the same form reference that worked before will cause an error -- unless you evaluate that reference seperately.  This holds true for OpenRecordset statements, and also CurrentDB.Execute and Docmd.RunSQL."


ok ... here is the example.

Setup:
A form with RecordSource of tblEmp  (employees table - two fields EmpID, EmpName)
Textbox on the form bound to EmpID
A saved Query Def (qryParametersTest) in the database window with this SQL:

SELECT tblEmp.EmpID, tblEmp.EmpName
FROM tblEmp
WHERE (((tblEmp.EmpID)=[Forms]![frmTest].[txtEmpID]));

This function:

Public Function mQyrParmTest() As Boolean
   
    Dim rst As DAO.Recordset, qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs("qryParametersTest")
    With qdf
        .Parameters("[Forms]![frmTest].[txtEmpID]") = ([Forms]![frmTest].[txtEmpID])
        ' or Parameters(0) = ([Forms]![frmTest].[txtEmpID])
        Set rst = .OpenRecordset(dbOpenDynaset)
        MsgBox rst![EmpName]    'this will display the Employee name related to the ID currently on the form
    End With
    Set rst = Nothing
    Set qdf = Nothing
End Function

------

mx
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
You may get an error is you don't explicitly refer to the .Value property of that Parameter:

.Parameters("Your Param Name").Value = SomeValue

This doesn't happen every time, but I've had it happen on more than one user machine. Not sure why, but better safe than sorry.

Top Expert 2010

Commented:
Hello chobe,

In order to pass in parameter values via DAO, I am pretty sure you must explicitly declare those parameters
in the query's SQL statement.

For example, instead of:

SELECT
FROM
WHERE ;

use:

PARAMETERS [StartDate] datetime, [EndDate] datetime;
SELECT
FROM
WHERE ;

Regards,

Patrick
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are opening the query twice:

   Set rs = qdf.OpenRecordset()
    With rs
        ' Skip the DoCmd.
        ' DoCmd.OpenQuery "qryRpt_ByRelease"
        '
        ' Do something (run a loop?) with your recordset.
        '
        .Close
    End With

/gustav

Author

Commented:
OK, tried the following 3 options using (hopefully correctly) the different suggestions offered - none of which I can get to work.  Options 1 and 2 still prompt for StartDT and EndDT even though ?qdf.Parameters([StartDT]) in intermediate window returns date value.  After removing criteria and Parameter definitions from stored procedure "qryRpt_ByRelease", I was finally able to address using a different approach.  I think this will work but if you see any problems please let me know.
    Option1
    Dim chuck As Variant
    Dim StartDT As Date
    Dim EndDT As Date
    txtStartDT = cboReleaseMonth.Column(2)
    txtEndDT = cboReleaseMonth.Column(3)
    Dim DB As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Set DB = CurrentDb
    Set qdf = DB.QueryDefs("qryRpt_ByRelease")
    With qdf
        .Parameters("StartDT").Value = ([Forms]![Startup].[txtStartDT])
        .Parameters("EndDT").Value = ([Forms]![Startup].[txtEndDT])
        Set rs = .OpenRecordset(dbOpenDynaset)
    End With
    DoCmd.OpenQuery ("qryRpt_ByRelease")

'    Option2
    Dim StartDT As Date
    Dim EndDT As Date
    txtStartDT = cboReleaseMonth.Column(2)
    txtEndDT = cboReleaseMonth.Column(3)
    Dim DB As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Set DB = CurrentDb
    Set qdf = DB.QueryDefs("qryRpt_ByRelease")
    With qdf
        .Parameters(0) = ([Forms]![Startup].[txtStartDT])
        .Parameters(1) = ([Forms]![Startup].[txtEndDT])
        Set rs = .OpenRecordset(dbOpenDynaset)
    End With
    DoCmd.OpenQuery ("qryRpt_ByRelease")

'    Option3_Click()
    Dim StartDT As Date
    Dim EndDT As Date
    txtStartDT = cboReleaseMonth.Column(2)
    txtEndDT = cboReleaseMonth.Column(3)
    StartDT = [Forms]![Startup].[txtStartDT].Value
    EndDT = [Forms]![Startup].[txtEndDT].Value
    Dim DB As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset
    Set DB = CurrentDb
    Set qdf = DB.CreateQueryDef("tmp_ByRelease", _
    "SELECT * FROM qryRpt_ByRelease WHERE [Install Date] " & _
    "BETWEEN "#" & [StartDT] & "# AND #" & [EndDT] & "#")
    DoCmd.OpenQuery "tmp_ByRelease"

Open in new window

Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
If your goal is to simply open a Recordset, why bother with QueryDefs at all? Just open the RS:

Dim rs As DAO.Recordset
Set rs = Currentdb.OpenRecordset("SELECT * FROM qryRpt_ByRelease WHERE [Install Date] " & _
    "BETWEEN "#" & txtStartDt & "# AND #" & txtEndDt & "#")
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are still opening the query twice. Modify as shown:

    Set rs = qdf.OpenRecordset()
    With rs
        '
        ' Do something (run a loop?) with your recordset.
        '
        .Close
    End With

/gustav

Author

Commented:
Your suggestion is what worked best for me.  question...Sorry it took so long to respond  Thanks so much for your assistance