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
|
|||
|
|||
Parameters for a Report (HELP!)
Help! I have been trying to resolve this issue for about a day now.
I have parameters that work fine in a crosstab query, pulling the values from a dialog box that does not close when the button for the query (and also for a report I am about to talk about) are pressed. The query works fine as long as there is data for the entire range (from the specified StartingDate to the Specified EndingDate). The problem I am having is in passing these values to a report. I have picked up bits and pieces that perhaps I can get the report using the parameters as specified in the Query that refer to the fields on the dialog box. Other places I have read that the parameters can be passed in the DoCmd.OpenReport Where clause. I have tried both methods and both together to no avail. When the report button is pressed, the parameter request boxes still pop up. The report will work fine if the values supplied in these boxes are for a date range that contains data for each date in two required tables (a different matter that I have resolved with some effort). How can I get the report to pull the StartingDate and EndingDate from the Dialog box like the Crosstab query does? Here is my attempt at this solution that does not work (the Parameter requests still come up): strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , "[Date] BETWEEN [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![StartingDate]" I would appreciate any help anyone can offer. Thank you. Vincent DeLuca |
#2
|
|||
|
|||
Parameters for a Report (HELP!)
Assuming the from EmployeePullsDialogBox is open, I would use:
strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , _ "[Date] BETWEEN #" & [Forms]![EmployeePullsDialogBox]![StartingDate] & _ "# AND #" & [Forms]![EmployeePullsDialogBox]![StartingDate] & "#" -- Duane Hookom MS Access MVP "Vincent DeLuca" wrote in message ... Help! I have been trying to resolve this issue for about a day now. I have parameters that work fine in a crosstab query, pulling the values from a dialog box that does not close when the button for the query (and also for a report I am about to talk about) are pressed. The query works fine as long as there is data for the entire range (from the specified StartingDate to the Specified EndingDate). The problem I am having is in passing these values to a report. I have picked up bits and pieces that perhaps I can get the report using the parameters as specified in the Query that refer to the fields on the dialog box. Other places I have read that the parameters can be passed in the DoCmd.OpenReport Where clause. I have tried both methods and both together to no avail. When the report button is pressed, the parameter request boxes still pop up. The report will work fine if the values supplied in these boxes are for a date range that contains data for each date in two required tables (a different matter that I have resolved with some effort). How can I get the report to pull the StartingDate and EndingDate from the Dialog box like the Crosstab query does? Here is my attempt at this solution that does not work (the Parameter requests still come up): strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , "[Date] BETWEEN [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![StartingDate]" I would appreciate any help anyone can offer. Thank you. Vincent DeLuca |
#4
|
|||
|
|||
Parameters for a Report (HELP!)
One of the resulting columns of your crosstab MUST be named Date. What is
the SQL view of your Crosstab? -- Duane Hookom MS Access MVP "Vincent DeLuca" wrote in message ... Thank you Duane. This is identical to a response I received from a different forum moments ago. However, I am still having the same result, the parameter boxes popping up. Could this be because of some setting in the parameter crosstab query upon which the report is being based or some other factor? I have verified the name of the form and controls involved. Thanks again. Vince "Duane Hookom" wrote: Assuming the from EmployeePullsDialogBox is open, I would use: strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , _ "[Date] BETWEEN #" & [Forms]![EmployeePullsDialogBox]![StartingDate] & _ "# AND #" & [Forms]![EmployeePullsDialogBox]![StartingDate] & "#" -- Duane Hookom MS Access MVP "Vincent DeLuca" wrote in message ... Help! I have been trying to resolve this issue for about a day now. I have parameters that work fine in a crosstab query, pulling the values from a dialog box that does not close when the button for the query (and also for a report I am about to talk about) are pressed. The query works fine as long as there is data for the entire range (from the specified StartingDate to the Specified EndingDate). The problem I am having is in passing these values to a report. I have picked up bits and pieces that perhaps I can get the report using the parameters as specified in the Query that refer to the fields on the dialog box. Other places I have read that the parameters can be passed in the DoCmd.OpenReport Where clause. I have tried both methods and both together to no avail. When the report button is pressed, the parameter request boxes still pop up. The report will work fine if the values supplied in these boxes are for a date range that contains data for each date in two required tables (a different matter that I have resolved with some effort). How can I get the report to pull the StartingDate and EndingDate from the Dialog box like the Crosstab query does? Here is my attempt at this solution that does not work (the Parameter requests still come up): strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , "[Date] BETWEEN [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![StartingDate]" I would appreciate any help anyone can offer. Thank you. Vincent DeLuca |
#5
|
|||
|
|||
Parameters for a Report (HELP!)
Here it is, Duane:
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; Vince "Duane Hookom" wrote: One of the resulting columns of your crosstab MUST be named Date. What is the SQL view of your Crosstab? -- Duane Hookom MS Access MVP "Vincent DeLuca" wrote in message ... Thank you Duane. This is identical to a response I received from a different forum moments ago. However, I am still having the same result, the parameter boxes popping up. Could this be because of some setting in the parameter crosstab query upon which the report is being based or some other factor? I have verified the name of the form and controls involved. Thanks again. Vince "Duane Hookom" wrote: Assuming the from EmployeePullsDialogBox is open, I would use: strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , _ "[Date] BETWEEN #" & [Forms]![EmployeePullsDialogBox]![StartingDate] & _ "# AND #" & [Forms]![EmployeePullsDialogBox]![StartingDate] & "#" -- Duane Hookom MS Access MVP "Vincent DeLuca" wrote in message ... Help! I have been trying to resolve this issue for about a day now. I have parameters that work fine in a crosstab query, pulling the values from a dialog box that does not close when the button for the query (and also for a report I am about to talk about) are pressed. The query works fine as long as there is data for the entire range (from the specified StartingDate to the Specified EndingDate). The problem I am having is in passing these values to a report. I have picked up bits and pieces that perhaps I can get the report using the parameters as specified in the Query that refer to the fields on the dialog box. Other places I have read that the parameters can be passed in the DoCmd.OpenReport Where clause. I have tried both methods and both together to no avail. When the report button is pressed, the parameter request boxes still pop up. The report will work fine if the values supplied in these boxes are for a date range that contains data for each date in two required tables (a different matter that I have resolved with some effort). How can I get the report to pull the StartingDate and EndingDate from the Dialog box like the Crosstab query does? Here is my attempt at this solution that does not work (the Parameter requests still come up): strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , "[Date] BETWEEN [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![StartingDate]" I would appreciate any help anyone can offer. Thank you. Vincent DeLuca |
#6
|
|||
|
|||
Parameters for a Report (HELP!)
You don't have a field named Date in the result columns of your crosstab so
you can't filter on it for the report. You would have to leave off the Date field in the DoCmd.Openreport where clause. Your date parameters are already being applied directly in your query. -- Duane Hookom MS Access MVP "Vincent DeLuca" wrote in message ... Here it is, Duane: 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; Vince "Duane Hookom" wrote: One of the resulting columns of your crosstab MUST be named Date. What is the SQL view of your Crosstab? -- Duane Hookom MS Access MVP "Vincent DeLuca" wrote in message ... Thank you Duane. This is identical to a response I received from a different forum moments ago. However, I am still having the same result, the parameter boxes popping up. Could this be because of some setting in the parameter crosstab query upon which the report is being based or some other factor? I have verified the name of the form and controls involved. Thanks again. Vince "Duane Hookom" wrote: Assuming the from EmployeePullsDialogBox is open, I would use: strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , _ "[Date] BETWEEN #" & [Forms]![EmployeePullsDialogBox]![StartingDate] & _ "# AND #" & [Forms]![EmployeePullsDialogBox]![StartingDate] & "#" -- Duane Hookom MS Access MVP "Vincent DeLuca" wrote in message ... Help! I have been trying to resolve this issue for about a day now. I have parameters that work fine in a crosstab query, pulling the values from a dialog box that does not close when the button for the query (and also for a report I am about to talk about) are pressed. The query works fine as long as there is data for the entire range (from the specified StartingDate to the Specified EndingDate). The problem I am having is in passing these values to a report. I have picked up bits and pieces that perhaps I can get the report using the parameters as specified in the Query that refer to the fields on the dialog box. Other places I have read that the parameters can be passed in the DoCmd.OpenReport Where clause. I have tried both methods and both together to no avail. When the report button is pressed, the parameter request boxes still pop up. The report will work fine if the values supplied in these boxes are for a date range that contains data for each date in two required tables (a different matter that I have resolved with some effort). How can I get the report to pull the StartingDate and EndingDate from the Dialog box like the Crosstab query does? Here is my attempt at this solution that does not work (the Parameter requests still come up): strDocName = "WeeklyPullsReport" DoCmd.OpenReport strDocName, acViewNormal, , "[Date] BETWEEN [Forms]![EmployeePullsDialogBox]![StartingDate] AND [Forms]![EmployeePullsDialogBox]![StartingDate]" I would appreciate any help anyone can offer. Thank you. Vincent DeLuca |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Formatting 'date' parameters to report | SimpleTechie | Setting Up & Running Reports | 1 | July 14th, 2004 09:32 PM |
6 Tables, 1 Report, W/O 6 Qrys | Andy | Setting Up & Running Reports | 9 | June 29th, 2004 09:52 PM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |
Report with multiple date parameters | Sabra Briere | General Discussion | 7 | June 20th, 2004 09:55 PM |
Parameters in a Report. | Amy Johnson | Setting Up & Running Reports | 2 | May 20th, 2004 01:05 AM |