CODEWITHSUNDEEP
vbams-access
Scotch
Scotch

Reputation: 3226

Entering duplicate query parameters for report

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?

Upvotes: 3

Views: 121

Answers (1)

HansUp
HansUp

Reputation: 97101

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().

Upvotes: 1

Related Questions