How do you pass in date parameters to a query 1

[Pack10]

I have a query that I am calling thru code.
Set db = CurrentDb()
Set qdf = db.QueryDefs("Manager_Review")
qdf.Parameters(0) = >= forms!ReportForm!startDate And <= Forms!ReportForm!EndDate

Set rs = qdf.OpenRecordset

I dont know how to formulate the Parameters(0).
I am getting expected expression. I normally use this for 1 value.

Thanks

 

[lameid]

First I would use named parameters.
Second your operators go in the query not as part of the parameter. The paramater is only a value in the query.

qdf.Parameters("StartDate") = Forms!ReportForm!StartDate

 

[Pack10]

Sorry, I am a little confused. My query has a date field (Task_Received) that I pass in the >= start date and the <= end date from the form.


is this what I need:

qdf.Parameters("txtStartDate") = forms!frmReportingTool!Task_Received

qdf.Parameters("txtEndDate") = forms!ReportingTool!Task_Received

 

[PHV]

Use the Filter property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Pack10,
Can you share the SQL view of the query. I prefer to never use parameters. I typically build my SQL dynamically.

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Sure I can in the morning.
This query is used for part of a report.
The query contains a date field and it contains
The start date and end date values coming in
From a form.
I am calling a function from the report to run
That query. But I need it filtered by the values
Entered on the form

 

[Pack10]

ok here is the code

Set db = CurrentDb()
Set qdf = db.QueryDefs("Manager_Review_Greg")
qdf.Parameters(0) = Forms![frmReportingTool]![txtStartDate]
qdf.Parameters(1) = Forms![frmReportingTool]![txtEndDate]

Set rs = qdf.OpenRecordset
rs.Filter = "[Task_Received] >= " & "#" & qdf.Parameters(0) & "#" & " And " & [Task_Received] <= "#" & qdf.Parameters(1) & "#"


I haven't found exactly what I need in any samples.
In debug, the values are correct in qdf.Parameters(0) and (1)

 

[PHV]

As Duane already asked, what is the SQL code of Manager_Review_Greg ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Pack10]

Here ya go:

SELECT Assigned_DDQs.Parent, Status_History.TransactionDate, Status_History.Status, Assigned_DDQs.Manager_Assigned
FROM Assigned_DDQs
INNER JOIN Status_History ON Assigned_DDQs.Parent = Status_History.Parent
WHERE (((Assigned_DDQs.Manager_Assigned)="Greg") AND ((Assigned_DDQs.Task_Received)>=[forms]![frmReportingTool]![txtStartDate] And (Assigned_DDQs.Task_Received)<=[forms]![frmReportingTool]![txtEndDate]));

 

[dhookom]

I would use code to remove the parameters like:

Dim db As DAO.Database
Dim qdf as DAO.QueryDef
Dim rs AS DAO.Recordset
Dim strSQL as String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Manager_Review_Greg")
strSQL = "SELECT Assigned_DDQs.Parent, Status_History.TransactionDate, " & _
    "Status_History.Status, Assigned_DDQs.Manager_Assigned " & _
    "FROM Assigned_DDQs " & _
    "INNER JOIN Status_History ON Assigned_DDQs.Parent = Status_History.Parent " & _
    "WHERE Assigned_DDQs.Manager_Assigned='Greg' AND Assigned_DDQs.Task_Received>=#" & _
    [forms]![frmReportingTool]![txtStartDate] & "# And Assigned_DDQs.Task_Received<=#" & _
    [forms]![frmReportingTool]![txtEndDate] & "#"
qdf.SQL = strSQL
Set rs = qdf.OpenRecordset

Duane
Hook'D on Access
MS Access MVP

 

[Pack10]

Thank you very much, I see how to do this now and it works like a charm. No parameters!!!!