Parameters for a Report (HELP!)

[Vincent DeLuca]

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

 

[Duane Hookom]

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

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]"

 

[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
(e-mail address removed)

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

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

 

[Duane Hookom]

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

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
(e-mail address removed)

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

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

 

[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

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

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
(e-mail address removed)

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


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

 

[Duane Hookom]

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

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

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

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
(e-mail address removed)

:

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


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