Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004
    Location
    Lorain, OH
    Posts
    11

    Unanswered: Parameter Reports

    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.

    Any help would be appreciated.

    Thanks.

    Vincent DeLuca
    VDeluca7@aol.com

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    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.



  3. #3
    Join Date
    Jul 2004
    Location
    Lorain, OH
    Posts
    11
    Quote Originally Posted by CyberLynx
    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
    PIVOT DayOfWeekList.DayOfWeek;

    Thanks for your input.

    Vincent DeLuca
    VDeluca7@aol.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •