Pass parameters to multiple queries in Access 2010?

Question

I have two queries that both prompt the user for a start and end date. I would like to package these queries up into one report (or something similar) so that the user can just push a button on the dashboard, it prompts them for a start date/end date once (passing it to both queries), and then prints the query results. Is this possible? I've been reading on reports, forms, and macros. I think I'm just starting to confuse myself.

Answer 1

If this is in a report you can omit the filter from your query to which your report is bound and instead use the report filters.

If you put your report as a subreport object on a form then you can have your StartDate and EndDate parameters be date pickers on the parent form. Then you can either bind them to the sub report using Link Master/Child Fields or you can construct a more elaborate filter and apply that filter to the child report every time your Start/End date controls are updated.

Answer 2

I prefer to use TempVars to set values that are used in multiple queries.

You can assign a TempVar through VBA or by using macro's. You can refer to TempVars in a query.

Use a TempVar in a query:

SELECT Something
FROM MyTable
WHERE MyTable.StartDate > TempVars!StartDate

Set a TempVar using a macro:

Set a TempVar using VBA:

Public Sub SetTempvar()
    TempVars!StartDate = #01-01-2001#
End Sub

Answer 3

This is one approach:

Create a global variable like `MyParameter' and a global function like this:

Public Function GetMyParameter() as [whatever type your parameter is]
  GetMyParameter = MyParameter
End Function

On the queries useGetMyParameter() for the parameter and on the button's click event use:

MyParameter = [Whatever value you want to assign to Parameter]
DoCmd.OpenQuery "Query1"
DoCmd.OpenQuery "Query2"

For start and end dates you need two parameters. You can call them GlobalStartDate and GlobalEndDate and the corresponding functions could be GetGlobalStartDate and GetGlobalEndDate. Then your query criteria can use something like BETWEEN GetGlobalStartDate() AND GetGlobalEndDate()