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

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

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

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

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

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