Entering duplicate query parameters for report

Question

I'm building a report at the moment that needs to have multiple aggregate queries (mostly sums and counts of different things).

I built a main report and have subreports that contain each individual query. All of these queries have parameters for a date range (which will be the same).

So, when I pull my report up, it asks about 12 times for the same parameters (start and end date). What is an effective solution to this problem?

The ugly workaround (for lack of a better solution) I was planning on using is to have the queries reference a form value. example :

SELECT count(*) FROM tblTests 
WHERE testdate >= [Forms]![formReportParams]![startDate] 
    AND testDate <= [Forms]![frmReportParams]![EndDate]

Is there a less sloppy solution that would allow all of the queries for my seperate subreports to share the same query params?

I suppose that you could possibly store the values in a table before running the report and then just do a dlookup on those values. This isn't really that much cleaner except the when you debug a query you won't have to have one certain form open. — HelloW
Well, it's not necessarily sloppy, I just don't like having the users navigate through like 3 forms to access reports. I suppose when they select this report, I'll display a form for them to enter date parameters and then open the report (referencing the controls on the form in the report queries). I just tend to avoid having my queries reference controls on a form when possible. Something about it doesn't feel right. — Scotch
Where will the date for a function come from? I think you will find a form is familiar and easier to control. — Fionnuala

HansUp HansUp · Accepted Answer · 2012-11-09T19:42:18

You could create separate functions for start and end dates, and use those functions in your query WHERE clauses.

Function GetStartDate(Optional ByVal pReset As Variant) As Date
    Static dteStart As Date
    If Not IsMissing(pReset) Then
        dteStart = pReset
    End If
    GetStartDate = dteStart
End Function

Initialize the function's static date value, and it will persist as the return value until you change it again. (However it's only persisted with the current Access application session.)

GetStartDate #2012-01-01#

Then use the function in your queries.

SELECT count(*) FROM tblTests 
WHERE testdate >= GetStartDate();

Do the same for GetEndDate().

Entering duplicate query parameters for report

1 Answers