How do I pass a parameter to a report

[EPNICO]

Hello Everyone:

Just a short question how do I pass the parameters entered in a query to a report so I can print it on the header? It is the starting date and ending date that the op. enters in the query and I need these dates to be printed on the report. Any ideas. Thanks.

epnico

 

[grnzbra]

are you using a parameter query or did you create a dialog box?

 

[EPNICO]

Thanks for your response I'm using a parameter query.

 

[grnzbra]

well, if you set the header fields to the max of the corresponding results in the query, you would get the ACTUAL start and end dates. If you need the actual parameters you put in, I don't know how you'd do it from a parameter query.

To solve that problem, I created global variables, opened a form in the OnOpen event of the report (the call to the form MUST incluce the instruction to open in dialog mode - acDialog or something). Then, in the click event of a command button on the form, set the globals equal to the values in the textboxes of the form, and close the form. You need to create two functions to read the globals. set the parameters of the report driving query equal to the functions that read the form. also set the header fields equal to the functions.

the result will be a call to the report which opens the form. you enter the dates in the form and click the button. the values will be transferred to the globals, the form will close and the the report will finish opening using the the parameters you entered.

Advantages, you can see if you've entered the parameters correctly before you push the button and you are only asked once. You cluld also include code for validation purposes.

 

[grnzbra]

in first line, that should be set the fields to the max and min values or the date field.

 

[Nvijayk]

Hi

Just another way perhaps if I understood the requirement.
I assume in the query design you would have typed in the criteria box under the Date field ' Between [Start Date] And [End Date]'to apply the parameter.

If this is the case, in your report you can, in the header, add 2 text box controls and set the control source as Start date and End date respectively for each of them. This will do the job.

all the best

Vijay

 

[EPNICO]

Thanks for your ideas grnzbra and nvijayk.

Can you expand your idea as to a control box since start date and end date in the query in the control source do they show up?

 

[ahg]

1. Make a small form - Form1. Add two unbound fields [BeginDate] and [EndDate]. Place two buttons on the form, one to Close, the second to open the Report.
2. In the reports query enter Between [Form1]![StartDate] and [Form1]![EndDate] in the criteria field of the Date column.
In the Report add an unbound field and place the following in the Control Source property
="For period beginning" & " " & Format([Forms]![Form1]![Begin Date],'mmm d", "yyyy') & " and ending " & Format([Forms]![Form1]![End Date],'mmm d", "yyyy')
That works for me!

Tony