How to limit Parameters

[Emma]

Hi I have a report with 3 sub reports, unfortunately each subreport comes
from a query which askes for start date and end date. So the outside form
when I run it askes for the main form's start date - end date
advocacy's start date - end date
financial's start date - end date
and information's start date - end date
So this is very tedious as the user is being asked for 8 dates instead of 2.
I would like the subforms to just take the main form's start date - end date
and use those dates for the parameters in the sub report queries. Can anyone
think of a fix?

 

[Emma]

Hi Kevin how does this work?

The queries that feed them can all reference and use the same two dates from
a single form.

Regards

Kevin

 

[Emma]

I'm having a problem with the instructions. When I click on the report
button, the date range form isn't popping up. Instead I'm getting
[Forms]![Date Range]![Start Date]. Also I created the macro, but it seems
that only the OK and CANCEL are being used.

 

[Clifford Bass]

Hi Emma,

The form must be open before you run the query or open the report.
Running the query/report will not cause it to open. Typically what one does
is display the form to the user; the user enters the parameters; then the
user clicks on a button that says something like "Print Report" or "Show
Report", depending on if you want to print or just display it.

Clifford Bass

 

[Emma]

Hi Clifford,

I have the form openning up I enter the date and time then press ok and I
get the following error message:

"The expression you entered has a function name that DatabaseName can't find."

Any ideas as to what is wrong?

Hi Emma,

The form must be open before you run the query or open the report.
Running the query/report will not cause it to open. Typically what one does
is display the form to the user; the user enters the parameters; then the
user clicks on a button that says something like "Print Report" or "Show
Report", depending on if you want to print or just display it.

Clifford Bass

I'm having a problem with the instructions. When I click on the report
button, the date range form isn't popping up. Instead I'm getting
[Forms]![Date Range]![Start Date]. Also I created the macro, but it seems
that only the OK and CANCEL are being used.

 

[Clifford Bass]

Hi Emma,

Does the query work directly when you have the form open and dates
entered? If so, post the code or macro for your OK button. If not, please
post the SQL of the query for the report.

Clifford Bass

 

[Emma]

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].[Last
Name], [Tbl Client Information].[First Name], [Tbl Client
Information].[Housing Risk], [Tbl Client Information].[Session Start Date],
[Tbl Client Information].Status, [Tbl Client Information].[Date Referred],
[Tbl Client Information].[Discharge Date], [Tbl Client
Information].Terminationd, Format([Session Start Date 2],"yyyy\:mm") &
[Client ID] AS Expr4
FROM [Tbl Client Information]
WHERE ((([Tbl Client Information].[Session Start Date]) Between
[Forms]![Date Range]![Start Date] And [Forms]![Date Range]![End Date]) AND
(([Tbl Client Information].Status)="Active"))
ORDER BY [Tbl Client Information].[Housing Risk];

 

[Clifford Bass]

Hi Emma,

That is odd. I do not see anything that would cause a problem. I
wonder if there is database corruption. As a start, make a backup copy of
your database then try a compact and repair. Then see if the query will
work. If not, make a copy of the query in your database. Using the copy,
try removing the Expr4 (Format...) column. Does that make a difference? If
not, try removing the condition on the start date. Does that make a
difference? Let me know the results.

Clifford Bass

SELECT [Tbl Client Information].[Client ID], [Tbl Client Information].[Last
Name], [Tbl Client Information].[First Name], [Tbl Client
Information].[Housing Risk], [Tbl Client Information].[Session Start Date],
[Tbl Client Information].Status, [Tbl Client Information].[Date Referred],
[Tbl Client Information].[Discharge Date], [Tbl Client
Information].Terminationd, Format([Session Start Date 2],"yyyy\:mm") &
[Client ID] AS Expr4
FROM [Tbl Client Information]
WHERE ((([Tbl Client Information].[Session Start Date]) Between
[Forms]![Date Range]![Start Date] And [Forms]![Date Range]![End Date]) AND
(([Tbl Client Information].Status)="Active"))
ORDER BY [Tbl Client Information].[Housing Risk];

Hi Emma,

Does the query work directly when you have the form open and dates
entered? If so, post the code or macro for your OK button. If not, please
post the SQL of the query for the report.

Clifford Bass