Friday, July 22, 2011

Showing Query Parameters in a Report

A parameter query is one which asks the user for input.  For instance, suppose I have a query that pulls records for a date range. Suppose further, I want the query to ask me for a Start Date and End Date for the range.  I can create a query like this:

SELECT * FROM MyTable WHERE TheDate BETWEEN [Enter Start Date] AND [Enter End Date]

Running this query will bring up two dialog boxes:

image  image

Entering the values in the boxes will return the records in that range.

But if I create a Report based on this query, how do I show the selected date range?  After all, reports show information from records in the query’s record source, and Parameter values aren’t included.

There are a couple of possibilities.

Method 1: Read Parameters Directly

The first is to read the parameter directly.  To do that, I just put a text box on my report for each query parameter. In the control source for the text boxes, I put the query parameter preceded by an equal sign.

For instance, in the case of the above query, the control sources for my two text boxes will be:
=[Enter Start Date] and =[Enter End Date]

Alternately, I could have a single text box with your dates concatenated:
=[Enter Start Date] & " - " & [Enter End Date]

I can also fancy it up a bit like this:
=”Date Range: “ & [Enter Start Date] & " to " & [Enter End Date]

Method 2: Read Values Returned

The second option is to read the actual minimum and maximum values from the records in the record source.  I can use the Min and Max functions of the report to do that.  Again, in the Control Source property of a text box:

=Min([Enter Start Date]) & " - " & Max([Enter End Date])

I can, of course, put them in separate text boxes as well.

Comparing The Methods

So what’s the difference between the two methods?

The first method returns the date range requested.  The second method returns the date range actually returned.

Isn’t that the same thing?  Not necessarily.  Suppose I request data from 1/1/2010 to 7/1/2011, but my table only has data starting with 2/1/2011.  The first method will return 1/1/2010 to 7/1/2011 (what I requested).  But the second method will return 2/1/2011 to 7/1/2011 (what’s actually in the record source of the report).


Unknown said...

this post was of great help to me. However now I am having to put my dates in twice, did I miss a step?

Anonymous said...

I am also trying to figure out how to not have to enter twice