Search code examples
ms-accessreportsubreport

MS Access 2010 input date once for multiple sub reports

I've been banging my head against this for weeks, have Googled every permutation of the question that i can think of, and have still got nowhere, so any help would be really appreciated.

WHAT I NEED: I need to generate a report, which pulls summaries of our referrals from the database. I have two reports which use the following queries as their Record Source:

SELECT referrals.origin_country, Count(*) AS ['number']
FROM referrals
WHERE (((referrals.referral_date) Between [Enter Start Date] And [Enter End Date:]))
GROUP BY referrals.origin_country;

And

SELECT referrals.first_language, Count(*) AS ['number']
FROM referrals
WHERE (((referrals.referral_date) Between [Enter Start Date:] And [Enter End Date:]))
GROUP BY referrals.first_language;

The queries are nearly identical, and the date range is the same for each one. The issues is that when I generate a report which uses these two reports as subreports, I then have to enter the date range for the subreports twice (once for the Country of Origin Subreport, and once for the First Language Subreport. My Access skills are not as advanced as I would like, and I'm wondering if anybody can tell me how to ensure that the user only has to enter the date range once? I've tried

  • Using VBA to create a variable onLoad, but then couldn't work out how to use this variable in the Record Source Query...
  • Using VBA to create a function which sets a variable onLoad, but then couldn't work out how to use the function in my Record Source query...

Any suggestions would be greatly appreciated!

Solution

  • Create a form with fields for the start and end date, and a button on it, which launches your main form. Use the 'Embedded Macro' Wizard to open the chosen Report On Click.

    Then, use [Forms]![FORM NAME]![FIELD NAME] in the query to access the date, for example:

    SELECT referrals.origin_country, Count(*) AS ['number']
FROM referrals
WHERE (((referrals.referral_date) Between [Forms]![EAL Referral Search Form]![dat_termly_report_start] And [Forms]![EAL Referral Search Form]![dat_termly_report_end]))
GROUP BY referrals.origin_country;