Your query is working just fine referencing a form to select some records:
but when you run it in code you get the dreaded 3061 "Too few parameters. Expected 1." message.
One option is to evaluate the Forms![Form name]![Field Name] expression as per the following examples.
The below example is an execute query.
Set db = CurrentDb
Set qdf = db.QueryDefs("LEMBatchesRpt Append to temp table")
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next i
qdf.Execute dbFailOnError
The below example is used in my standard routine which copies the contents of a recordset into an Excel spreadsheet. Due to the requirements of basing the openrecordset on a query to which I added a Where clause I created a temporary query and then did the parameter evaluation
strSQL = "SELECT * FROM [" & strQuery & "]"
If Len(strWhere) > 0 Then _
strSQL = strSQL & " WHERE " & strWhere & ";"
Set qdf = db.CreateQueryDef("", strSQL)
For i = 0 To qdf.Parameters.Count - 1
qdf.Parameters(i) = Eval(qdf.Parameters(i).Name)
Next i
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
….
' Insert recordset into cells starting at left hand side and second row
' Using a copyfromRecordset is much, much faster than stuffing in the cells one at a time.
.range("A2").CopyFromRecordset rs
This is used in a generic routine behind the very flexible reporting form in the Granite Fleet Manager. When the user clicks on the Export Report to Excel command button I look at the record source of the report to fetch the query name. I then build the where clause and execute the above code.