If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report Based Upon Parameter Query with Form References
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 |
#3
|
|||
|
|||
Report Based Upon Parameter Query with Form References
Vincent DeLuca wrote:
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. -- Marsh MVP [MS Access] |
#4
|
|||
|
|||
Report Based Upon Parameter Query with Form References
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 "Marshall Barton" wrote: Vincent DeLuca wrote: 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. -- Marsh MVP [MS Access] |
#5
|
|||
|
|||
Report Based Upon Parameter Query with Form References
Vincent DeLuca wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Display Parameter from Form on Report | sara | Setting Up & Running Reports | 10 | July 19th, 2004 04:54 PM |
Control value based on parameter query | ORSP | New Users | 2 | July 16th, 2004 05:42 PM |
Open a report based on which query I select | Sierras | Setting Up & Running Reports | 8 | June 24th, 2004 05:43 PM |
Need help! Parameter update query to form | Le Tran | New Users | 7 | June 8th, 2004 06:26 PM |
Query Form: Print Report | Dennis | Running & Setting Up Queries | 1 | June 6th, 2004 01:08 PM |