Report Based Upon Parameter Query with Form References

[Vincent DeLuca]

To All:

Would the data that is passed from the query that is based upon a parameter text box form be limited to the range specified by the parameter query as shown in a report based upon that query, or would you need to "pass along" the values to limit the data to the range desired within the report itself?

I have successfully created a parameter query based upon form text box data for the desired date range that displays the accurate data, but cannot get a report based upon that query to print. The same report when pulling the same data in which I type in the parameters in popup boxes (based upon a copy of the same query without form references) works fine. I cannot locate the reason for this.

The query with the references to the dialog box form text boxes has the following parameters:

[Forms]![EmployeePullsDialogBox]![StartingDate] Date/Time
[Forms]![EmployeePullsDialogBox]![EndingDate] Date/Time

And in the [Date] column of this crosstab query:

Where

Between [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![EndingDate]

The same form without the dialog box references has the following parameters:

StartingDate Date/Time
EndingDate Date/Time

Why would I be running into this problem?

Thanks.

Vincent DeLuca
(e-mail address removed)

 

[Vincent DeLuca]

I have reworded this problem more accurately--

To All:

I have a report based upon a crosstab query with parameters. The crosstab query works fine and pulls its values from a dialog box with two date values, a starting date and an ending date.

The problem I am having is that I cannot get a report based upon that query to print unless I type in the parameters that pop up.

How do I get the report to work based upon the values I type into the form without asking for those values again when I run the report?

The query with the references to the dialog box form text boxes has the following parameters:

[Forms]![EmployeePullsDialogBox]![StartingDate] Date/Time
[Forms]![EmployeePullsDialogBox]![EndingDate] Date/Time

And in the [Date] column of this crosstab query:

Where

Between [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![EndingDate]

The same form without the dialog box references has the following parameters:

StartingDate Date/Time
EndingDate Date/Time

Why would I be running into this problem?

Thanks.

Vincent DeLuca
(e-mail address removed)

 

[Marshall Barton]

To All:

Would the data that is passed from the query that is based upon a parameter text box form be limited to the range specified by the parameter query as shown in a report based upon that query, or would you need to "pass along" the values to limit the data to the range desired within the report itself?

I have successfully created a parameter query based upon form text box data for the desired date range that displays the accurate data, but cannot get a report based upon that query to print. The same report when pulling the same data in which I type in the parameters in popup boxes (based upon a copy of the same query without form references) works fine. I cannot locate the reason for this.

The query with the references to the dialog box form text boxes has the following parameters:

[Forms]![EmployeePullsDialogBox]![StartingDate] Date/Time
[Forms]![EmployeePullsDialogBox]![EndingDate] Date/Time

And in the [Date] column of this crosstab query:

Where

Between [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![EndingDate]

The same form without the dialog box references has the following parameters:

StartingDate Date/Time
EndingDate Date/Time

It sure sounds like you forgot to explicitly declare the
parameters in the query.

 

[Vincent DeLuca]

Hi, Marsh-

Here is the SQL for the query:

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;

As you can see, the parameters have been explicitly declared. The query runs fine without requesting the parameter values. The report, hwoever, asks for the values no matter what. Still scrathing my head on this one...

Thanks.

Vince

To All:

Would the data that is passed from the query that is based upon a parameter text box form be limited to the range specified by the parameter query as shown in a report based upon that query, or would you need to "pass along" the values to limit the data to the range desired within the report itself?

I have successfully created a parameter query based upon form text box data for the desired date range that displays the accurate data, but cannot get a report based upon that query to print. The same report when pulling the same data in which I type in the parameters in popup boxes (based upon a copy of the same query without form references) works fine. I cannot locate the reason for this.

The query with the references to the dialog box form text boxes has the following parameters:

[Forms]![EmployeePullsDialogBox]![StartingDate] Date/Time
[Forms]![EmployeePullsDialogBox]![EndingDate] Date/Time

And in the [Date] column of this crosstab query:

Where

Between [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![EndingDate]

The same form without the dialog box references has the following parameters:

StartingDate Date/Time
EndingDate Date/Time

It sure sounds like you forgot to explicitly declare the
parameters in the query.

 

[Marshall Barton]

Here is the SQL for the query:

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;

As you can see, the parameters have been explicitly declared. The query runs fine without requesting the parameter values. The report, hwoever, asks for the values no matter what. Still scrathing my head on this one...

What is the exact prompt string that you see? If you
reference any undefined name in a report text box, you will
be prompted for a value. So if you have a text box that is
supposed to display the parameter value, make sure that it
is spelled exactly the same way as the parameter or is a
reference to a valid control on an open form.