Report based on query

[rkaptu]

HI

I am working on a report based on a query that displays data between 2 different dates.

The query is based on a table that includes one column with Inspection dates, and in the query I added a Criteria 'Between [Start_Date] and [End_Date]'. I also created two parameters in the Query Expr1:[Start_Date] and Expr2:[End_Date].

When report loads it asks me to enter manually the Start and End Date.

I would like to control this by loading a form in the Open_Report Event where dates can be entered with the date picker to make it easier and avoid invalid data. How can I do this?

So far I used this code, but it is still asking me the missing parameters as if there is nothing

Private Sub Report_Open(Cancel As Integer)

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim prm As DAO.Parameter
Dim qdf As DAO.QueryDef

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Task Schedule Query by Inspector")

qdf.Parameters.Refresh
qdf.Parameters("Start_DAte").Value = Date
qdf.Parameters("End_Date").Value = Date + 1

Set rs = qdf.OpenRecordset()

End Sub

Thanks in advance

 

[Ranman256]

have you tried the report using a query? (NOT code)

 

[sxschech]

To create the report using a parameter on a form you would modify your query to point to the field on the form. You shouldn't need to create a recordset.

Here is an example from one of my queries.

WHERE qryEventEvaluations.EndDate =[Forms]![frmSurveys].[txtEventDate]

If the form is open, then when you run the report the criteria will be based on what is in the form field. If the form is closed, a parameter will come up and you can manually enter the information.

Also, if your query is actually named "Task Schedule Query by Inspector"
would suggest either removing the spaces or using underscores:
TaskScheduleQuerybyInspector
Task_Schedule_Query_by_Inspector

Otherwise may have issues down the road as will need to put brackets around it.

Then you put the query name in the Recordsource of the report instead of the vba recordset.

 

[rkaptu]

Thanks for all your replies.

So I have created a form from which I call the report. In the form user can enter date range, which I am passing to the report as an openarg, as shown below.

DoCmd.OpenReport "weekly_report", acViewReport, , , , Me.DateRange & "|" & inspectors

Where Me.DateRange refers to the value of a textbox in the form. Inspectors is a string listing the inspectors for whom report is needed.

In the Open Report Event I confirmed that the OpenArg is coming through correctly and I managed to separate the date range from the rest.

Now the query uses the 2 variables Start_date and End_Date to limit the records shown. How do I assign value to these 2 variables? I change query name to Weekly_Report_Query

Thanks