CODEWITHSUNDEEP
sqldatabasems-accessms-access-2010
Plaxerous
Plaxerous

Reputation: 143

MS Access - Extracting Min/Max Year from a Union Query onto a Report

I have a union query that pulls objects and their dates from a query and table with similar fields so that I can display them together on a report. The union query (qry_ObjectUnion) has a WHERE function that prompts the user to enter a start and end date.

SELECT [Object], [DateObject]
FROM qry_ObjectQuery
WHERE (((Year([DateObject])) Between [Which year would you like this report to start?] And [Which year would you like this report to end?]))

UNION SELECT [Object], [DateObject]
FROM tbl_ObjectTable
WHERE (((Year([DateObject])) Between [Which year would you like this report to start?] And [Which year would you like this report to end?]));

On the report, I’d like to dynamically display the start and end year the user chose for the report. To accomplish this, I wrote a second query (qry_ObjectUnionYears) off of the union query.

SELECT Min(Year([DateObject])) AS MinYear, Max(Year([DateObject])) AS MaxYear
FROM qry_ObjectUnion;

The second query returns the start and end year as expected when accessing (double-clicking) the query directly, but opening the report yields different results. In the report, I have a textbox with a MinYear control source of:

=[qry_ObjectUnionYears]![MinYear]

When accessing the report, the first two message boxes ask for the start and end year, but the third message box asks for a parameter value for qry_ObjectUnionYears and proceeds to return a #Error no matter what I enter.

Can anyone help me out? Thanks in advance!

Upvotes: 0

Views: 44

Answers (1)

Plaxerous
Plaxerous

Reputation: 143

I figured it out after browsing online some more. The textbox can self-reference the parameters entered into the report as so:

=Report![ObjectReport]![Which year would you like this report to start?]

There is no need to write a second query as I originally tried to do.

Upvotes: 1

Related Questions