Send variables when calling query

[idono]

Hello, I have a query set up in my database that requires two dates. It is parameterized so it asks for the dates when it is run. The problem I am having is calling this query from code. It doesn't ask for the dates.

Set rs = db.OpenRecordset("qryClosedThisWeek&quot

produces the error: too few parameters expected two.

How can I send the dates when calling the query in VBA?

 

[jfgambit]

Are you calling the dates from code. Can you post all of your code so that we can review it?

Also, have you considered putting the date parameters in the query? Example: In the criteria of the query add Between [What is first date?] and [What is last date?]

 

[idono]

To answer your question, yes I have but parameters in the query. It still doesn't prompt me for the input. One reason for this may be the fact that When I call this query I have an Excel workbook open that the query information will be stored in.

Here is a snippet of the code.

'open the recordset for Closed ECN's
'Set rs = db.OpenRecordset("qryClosedToday&quot
Set rs = db.OpenRecordset("qryClosedThisWeek&quot

'Insert the data from the recordset
If rs.RecordCount <> 0 Then
rs.MoveFirst
Do Until rs.EOF
With objNewXLBook.ActiveSheet.Range(&quot;C&quot; & X & &quot;:&quot; & &quot;D&quot; & X)
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = True
End With

objNewXLBook.ActiveSheet.Cells(X, 1).Value = rs![Project]
objNewXLBook.ActiveSheet.Cells(X, 2).Value = rs![ECN]
objNewXLBook.ActiveSheet.Cells(X, 3).Value = rs![DESC]
objNewXLBook.ActiveSheet.Cells(X, 5).Value = rs![Drafter]
objNewXLBook.ActiveSheet.Cells(X, 6).Value = rs![Sales Order]
objNewXLBook.ActiveSheet.Cells(X, 7).Value = rs![Date Received]
objNewXLBook.ActiveSheet.Cells(X, 8).Value = rs![Start Date]
objNewXLBook.ActiveSheet.Cells(X, 9).Value = rs![Project Status]
objNewXLBook.ActiveSheet.Cells(X, 10).Value = rs![Hrs Est]
strActTime = &quot;SELECT Sum(tblTimePerPack.HrsWorked) AS Today &quot; & _
&quot;FROM tblTimePerPack WHERE fkECNID = &quot; & dQuote & _
rs![ECN] & dQuote & &quot;AND Date = &quot; & &quot;#&quot; & Date & &quot;#&quot;
Set ActTime = db.OpenRecordset(strActTime)
objNewXLBook.ActiveSheet.Cells(X, 11).Value = ActTime![Today]
ActTime.Close
Set ActTime = Nothing
strActTime = &quot;SELECT Sum(tblTimePerPack.HrsWorked) AS Total &quot; & _
&quot;FROM tblTimePerPack WHERE fkECNID = &quot; & dQuote & _
rs![ECN] & dQuote
'MsgBox strActTime
Set ActTime = db.OpenRecordset(strActTime)
objNewXLBook.ActiveSheet.Cells(X, 12).Value = ActTime![Total]
ActTime.Close
Set ActTime = Nothing

I hope this will help clear up what it is I'm trying to accomplish. I thought I remembered once reading a way to call a stored procedure and send the required parameters. Maybe I was dreaming???