By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,334 Members | 2,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,334 IT Pros & Developers. It's quick & easy.

How to print user-entered parameters on a report?

sueb
100+
P: 379
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?
Feb 17 '11 #1

✓ answered by 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:
    Expand|Select|Wrap|Line Numbers
    1. [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:
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. 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.

Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,627
  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:
    Expand|Select|Wrap|Line Numbers
    1. [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:
    Expand|Select|Wrap|Line Numbers
    1. 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:
    Expand|Select|Wrap|Line Numbers
    1. 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.
Feb 17 '11 #2

NeoPa
Expert Mod 15k+
P: 31,434
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 :
Expand|Select|Wrap|Line Numbers
  1. Private datFrom As Date, datTo As Date
  2.  
  3. Private Sub Report_Open()
  4.     On Error Resume Next
  5.     datFrom = CDate(Split(.Filter, "#")(1))
  6.     datTo = CDate(Split(.Filter, "#")(3))
  7. End Sub
Feb 17 '11 #3

ADezii
Expert 5K+
P: 8,627
@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?
Feb 17 '11 #4

NeoPa
Expert Mod 15k+
P: 31,434
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
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [Table]
  3. WHERE  ([DateField] Between [Enter Start Date] And [Enter Finish Date])
Alternatively :

RecordSource
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [Table]
Filter
Expand|Select|Wrap|Line Numbers
  1. "([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.
Feb 17 '11 #5

ADezii
Expert 5K+
P: 8,627
Thanks, my misinterpretation. I thought you were trying to extract the Filter Property in conjunction with the Parameter Query.
Feb 17 '11 #6

sueb
100+
P: 379
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!
Feb 17 '11 #7

NeoPa
Expert Mod 15k+
P: 31,434
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.
Feb 18 '11 #8

ADezii
Expert 5K+
P: 8,627
@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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Activate()
  2.   Me![Label1].Caption = "Sales for Acme Manufacturing between " & _
  3.                          Me![SDate] & " and " & Me![EDate]
  4. 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.
Attached Files
File Type: zip Parameters.zip (20.5 KB, 97 views)
Feb 18 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.