How to print user-entered parameters on a report?

[sueb]

I have several reports based on queries that require the user to enter a Start and End Date.

How do I access these parameters so I can print them on the report?

[ADezii]

1. Let's assume that in your Query that is the Data Source for the Report, you have the following Parameter in the Criteria Row of the [Start Date] Field:
   

   [Enter Start Date]

2. Create a Calculated Field in your Query that will retrieve the Value of the Parameter (must be an exact match), as in:
   

   sDate: [Enter Start Date]

3. Position a Text Box to wherever you would like the Start Date Parameter to appear on your Report.
4. Set the Control Source of the Text Box equal to the Calculated Field, as in:
   

   sDate

5. Now, whatever Value the User enters for the Start Date Prompt will be reflected in the Calculated Field (sDate). By setting the Control Source of a Text Box to this Field, the entered Value will be displayed in the Report.
6. Repeat the process for the End Date.
7. Should you have any trouoble with this, I can Attach a very simple Demo to illustrate this point.

[NeoPa]

Sue, that really depends very much on how you specify to the report the date range it should reflect. That should be in the question.

For now though, I will deal with the most common way of handling filtering within a form or report, which is with the .Filter parameter (It's designed for just that purpose and makes filtering simpler).

In such a scenario, you should know how the filter string will present itself. I would guess at something like "[RptDate] Between #m/d/yyyy# AND #m/d/yyyy#".

From this point it is up to you to parse the string so that you retrieve the dates contained therein. For such as this I would use code (in Report_Open()) similar to :

Private datFrom As Date, datTo As Date
 
Private Sub Report_Open()
    On Error Resume Next
    datFrom = CDate(Split(.Filter, "#")(1))
    datTo = CDate(Split(.Filter, "#")(3))
End Sub

[ADezii]

@NeoPa: I don't think that you can use the Report's Filter Property in this manner, can you? Technically, it is not the Report that is being Filtered, and you will receive an Empty String for the Filter Property, instead of the actual Query Parameter(s). FilterOn would not automatically be set to ON under these conditions, would it?

[NeoPa]

Yes you can ADezii. I have done (for a Form rather than a Report if I'm precise). This would not involve the use of a paramater query mind you, but rather a more straightforward, open, query where the filtering is expected to be applied externally. I'll illustrate the different concepts :

RecordSource

SELECT *
FROM   [Table]
WHERE  ([DateField] Between [Enter Start Date] And [Enter Finish Date])

Alternatively :

RecordSource

SELECT *
FROM   [Table]

Filter

"([DateField] Between #m/d/yyyy# And #m/d/yyyy#)"

Notice how this keeps the static SQL (SELECT & FROM clauses) where they don't need to be changed, and the more variable part (WHERE clause or Filter) easily manipulable without having to fiddle with the RecordSource. As a developer, it also keeps the two elements separate, so one can concentrate more easily on the element one is dealing with at the time. I hope that makes sense.

[ADezii]

Thanks, my misinterpretation. I thought you were trying to extract the Filter Property in conjunction with the Parameter Query.

[sueb]

So I would put the "between" statement in my report's query, and the "report open" subroutine in my report's OnOpen property, and then I would be able to use (from your instance) "datFrom" and "datTo" as controls on my report?

I'm going to try that and post back what I found.

Thanks!

[NeoPa]

Sue, in a case such as this, where two members offer differing solutions, it's better when replying, to make it clear whom you are replying to. In this case, reading between the lines, I expect you are responding to ADezii. Of course, it's always best to reply to all members who respond, even if only to say that you don't feel their suggestion suits you for some reason (even if it's as simple as that you don't really understand the suggestion), but that's down to you personally of course.

Conversations can get quite complicated though, if you don't at least make it clear to whom your message is directed.

[ADezii]

@sueb, I too am also not sure as to exactly whom you are addressing. Realizing that my approach may be a little confusing if you have never done it before, I decided to create a simple Demo to illustrate this point. I also demonstrated a second Method by which you can capture the actual Parameter Values via the Calculated Fields and assign them to a Label. This approach requires that you include both Calculated Fields in the Report, but make them invisible.

Private Sub Report_Activate()
  Me![Label1].Caption = "Sales for Acme Manufacturing between " & _
                         Me![SDate] & " and " & Me![EDate]
End Sub

In any event, both approaches are represented in the Attachment. Click on the Command Button, and when prompted for a Start and End Dates enter 3/1/2011 and 3/31/2011 respectively. This is for effect only. Keep in mind that you also have NeoPa's approach which now gives you three ways to accomplish what you have requested. Any questions, feel free to ask.