MS Access vba, open select query with parameters, run-time 3065 cannot execute a select query

Question

I am getting frustrated trying to open a select query with a "parameter" set. I fully suspect I am building my query all wrong. I want a form with an unbound date textbox for the user to input a date (I have this). Then I want that date passed to a prestored parameter select query and the query opens. If I open the query manually it asks for the [Compare Date] as expected. When I use the form, click a button to open the query the qdf.execute fails.

Fictitious query for demo

PARAMETERS [Compare Date] DateTime;
SELECT [Compare Date] AS Compare_date;

I know that "parameters" are usually in the WHERE clause but, again, this query works if run manually.

Then below is the calling code

Private Sub btnRunReport_Click()

    Const pstr_CURRENCY_QRY As String = "Query12"
    Const pstr_DATA_PARAM As String = "Compare Date"
    Dim db As DAO.Database: Set db = CurrentDb()
    Dim qdf As DAO.QueryDef
    Set qdf = db.QueryDefs(pstr_CURRENCY_QRY)
    With qdf
        .Parameters(pstr_DATA_PARAM).Value = CDate(Me.txtCompareDate.Value)
        .Execute dbFailOnError  ' <<<< Fails here
        .Close
    End With    ' qdf
    Set qdf = Nothing
    Set db = Nothing
End Sub

Can someone tell me how to fix this or come at it with a different approach so that the query works manually and via the form?

Just gives me the runtime error in the subject on the .execute line. No further information is given. I used the immediate window to confirm the parameter is being set and it is. It just will not open the select query — acr_scout
If it is a RunTime Error, you will be given an Error Number and Error Description. — PaulFrancis
I did, per the subject 3065 cannot execute select query @HansUp seems to be onto the problem in that it is not a parameter issue as it is the .Execute method cannot open a select query. — acr_scout

HansUp HansUp · Accepted Answer · 2015-05-14T14:54:51

You can't use the DAO Execute method for a SELECT query. That is why Access complains "Cannot execute a select query."

The parameter issue is not relevant here.

Decide what you want to do with the result set your parameter query returns. If you want to load it into a recordset, you can use the QueryDef's OpenRecordset method.

MS Access vba, open select query with parameters, run-time 3065 cannot execute a select query

1 Answers