How to use the same date range parameters for multiple reports

[Guest]

We have approximately 15 month-end accounting reports in Access. Each report
needs the same Start Date & End Date Parameters.

Is there a way to store the Parameter values so that they are available and
can be re-used by each report and we won't have to enter 30 Parameter values
(15 x 2)?

Here is a copy of a current query:

SELECT transactions.[Status Code], [SC Collection Services].Description,
transactions.[Trans Code], [TC Collection Services].Description,
Sum(transactions.[Trans Amt]) AS [SumOfTrans Amt]
FROM (transactions INNER JOIN [SC Collection Services] ON
transactions.[Status Code] = [SC Collection Services].[Status Code]) INNER
JOIN [TC Collection Services] ON transactions.[Trans Code] = [TC Collection
Services].[Trans Code]
WHERE (((transactions.[Date Posted]) Between #08/01/05# And #08/31/05#) AND
((transactions.[Client Number]) Is Null Or (transactions.[Client
Number])=33222))
GROUP BY transactions.[Status Code], [SC Collection Services].Description,
transactions.[Trans Code], [TC Collection Services].Description
ORDER BY transactions.[Status Code], transactions.[Trans Code];

I tried to create two single record tables (ReportStart & ReportEnd) and
store the Start Date and End Date in these tables. However, it caused some
problems with the queries (not in the aggregate function).

This seems like a fairly common problem, but I haven't found many answers yet.

Thanks for the help,

David Pineau

 

[Guest]

You can save the date value in a global parameter, and then return the value
using a function, you can use this function in all the queries

Create in a module
=========================
Variant declaration
Global MyDate as Date
==========================
Create a function
Function ReturnStartDate () as date
ReturnStartDate = MyDate
End Function
===========================
Use in a query
Select * From TableName Where DateField = ReturnStartDate ()

 

[John Vinson]

On Thu, 6 Oct 2005 12:31:01 -0700, "David P" <David

Is there a way to store the Parameter values so that they are available and
can be re-used by each report and we won't have to enter 30 Parameter values
(15 x 2)?

The simplest way is to use a Form to collect the criteria: name it
(say) frmCrit with textboxes txtStart and txtEnd, and use criteria of

= CDate([Forms]![frmCrit]![txtStart]) AND < DateAdd("d", 1, CDate([Forms]![frmCrit]![txtEnd]))

This protects you (at least partially) from odd date formats and from
table values containing a time portion along with a date.

Launch the Reports from a command button on the form, and the user
need only enter dates (which could even have default values) and then
click a button. No need to show them the Reports window at all!

John W. Vinson[MVP]