I am creating a parameter report. I have sucessfully created a crosstab query with parameters. I see that there is a where option in the DoCmd.OpenReport command where I can specify the criteria and also references to the dialog box form (non-closing dialog box) fields that contain the starting and ending date of the date range of the data I am looking for.
I know that with a crosstab query, I must have at least one row of data in each connected table or the report will not run. I am using tables with a complete week range of example data. The data works fine on the crosstab query.
The dillemna I am running into is this: How am I to write the SQL where statement to refer to the form fields? I tried to write a BETWEEN statement as works fine in the crosstab query. I cannot get this to work in the report statement. And as I am using the crosstab query as the datasource for this report, how do I pass these parameters successfully? The query grabs the data fields on the form correctly, but the report asks me for the values of the parameters. Does the report look at the query for these values? If not, what is the correct syntax for the where clause at the end of a DoCmd.OpenReport statement? I have tried a number of possibilities with no success.
Post the Query (SQL statement) so that we can take a look at it. It seems like you are referencing your form fields incorrectly within your SQL statement.
This is the SQL statement that works fine for the crosstab query. I am trying to figure out how to make the crosstab report (with the same data) take the startingdate and the endingdate from the dialog box fields like the query does and not ask the user for the values again when the report is run. The report runs fine if the user supplies the values assuming there is data in both referenced tables for each date in the range (the same requirement the crosstab query has).
PARAMETERS [Forms]![EmployeePullsDialogBox]![StartingDate] DateTime, [Forms]![EmployeePullsDialogBox]![EndingDate] DateTime;
TRANSFORM "~" & Sum([Pulls]) & "~" & Sum([Errors]) & "~" & Format(Avg([AccuracyPercent]),"0.00%") AS CheckPullsErrorsPercent
SELECT [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID
FROM [Workers Query (All)] INNER JOIN (DayOfWeekList INNER JOIN [WorkerPullsforCustomer Query] ON DayOfWeekList.DayNumber = [WorkerPullsforCustomer Query].Day) ON [Workers Query (All)].WorkerID = [WorkerPullsforCustomer Query].WorkerID
WHERE ((([WorkerPullsforCustomer Query].Date) Between [Forms]![EmployeePullsDialogBox]![StartingDate] And [Forms]![EmployeePullsDialogBox]![EndingDate]))
GROUP BY [Workers Query (All)].LastFirstMiddle, [WorkerPullsforCustomer Query].WorkerID