Passing Query Parameters to a report

[chris_jolly]

Hello All,

I have a query that the user selects the date range (parameters). I am running a report based on the query and would like to pass the query parameters, which have already been entered, to the report.

As of right now, the user must go through the date selection (parameters) again when trying to print the report.

What would be the easiest way to accomplish this?

 

[Uncle Gizmo]

Could you build the SQL string first in your form and then passed the SQL string including the parameters straight in to the report record source?

 

[chris_jolly]

Thanks for the reply.

Im not that good with SQL. This is my first database and I have self taught during the entire process.

Do you have an example of what you mean about building the sql?

Thanks

 

[Brianwarnock]

How are you entering the parameters?

I always use an unbound querydetailsform in which the user is prompted for the parameters and the query/report then run from a command button, these deatils remain available for use in the Report.

If you have not used a form in this way the attachement below, from another thread has a simple example.

I believe a discussion on how too achieve this from a prompt did take place on the forum a while ago but I don't know how to find it.

Brian


http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=18621&d=1188995285

 

[chris_jolly]

I have a switchboard of sorts that has a command button to open the form that houses the query. The standard "Enter Parameter" pop up box is how I have the user choose a start date and end date

 

[chris_jolly]

to the top

 

[neileg]

Don't use the parameter pop up box. Capture the user input in unbound controls in a form. This is what Brian is telling you.

 

[chris_jolly]

How would I link the unbound controls to fill the parameters??

 

[neileg]

The entry required in the criteria line of the query takes the following syntax:
Forms!MyFormName!MyControlName

 

[themurph2000]

Maybe I'm misreading this, but if you have a query that already has parameter values in it, wouldn't you just have the person run the report straight out? The report is basically the query in printable form, plus any sort of organizing you do in it. You can assign a Switchboard button to open up the report, at which point the parameter pop-up box would ask for the dates.

Now if you're using some sort of "Choose Date" calendar type of form, then ignore what I just said.

 

[neileg]

Patrick, the parameters aren't held anywhere if you allow Access to pop up the parameter box so there's nowhere for the report to reference them. This would lead to the same parameter boxes popping up every time you need the user input. Of course the user might not enter the same data and this would get very messy. If you use a form, you can keep the form open when the report runs and pick up the parameters from the form.

 

[themurph2000]

Patrick, the parameters aren't held anywhere if you allow Access to pop up the parameter box so there's nowhere for the report to reference them. This would lead to the same parameter boxes popping up every time you need the user input. Of course the user might not enter the same data and this would get very messy. If you use a form, you can keep the form open when the report runs and pick up the parameters from the form.

Ah, OK. I wasn't getting what he wanted with the data people entered, whether it needed to be stored or not. I was just curious to know why a person needed to bother running the query first, then run a report based on the same query. I thought seeing the report would be the equivalent of seeing the results of the query the report is based on.

This is probably still spitballing on my part, but I wonder if he can run a paramter make-table query and base his report off of that. Probably needs way better engineering than I know, though.

 

[cat001969]

If the report is based on the query, and the query is getting the parameter values from the form you only need to run the report, the report will run the query. You don't want to do make table queries every time you need the report. There is no purpose for that.

 

[themurph2000]

If the report is based on the query, and the query is getting the parameter values from the form you only need to run the report, the report will run the query. You don't want to do make table queries every time you need the report. There is no purpose for that.

Yeah, that's what I was saying originally. The question is: does the designer need to save the choices of the user when the user runs the report? If not, this is an easy solution. If they do, gets a bit tougher.

 

[chris_jolly]

Hey Guys,

Im having trouble linking the forms to the query. I took your advice and created a form with two text boxes for the input of the dates so that I can store them for my report. However, I cannot get it to work with query. NeilEg when you specified the Forms!MyFormName!MyControlSource! for the criteria, does that go into the criteria box in the query design?? I am using that code within a >= and <= to create a date range. When I enter the perameters it doesnt carry to the query. Is there something Im missing??

 

[chris_jolly]

I figured out how to gather the parameters using a form. Now I need to make the report use the parameters that I have the user input into text boxes.

The reports purpose is just to print out the data already entered to provide a way for the user to have a hard copy archive of the data.

Would the where funtion be the easiest way to do this?
Can you provide an example for me please

 

[neileg]

NeilEg when you specified the Forms!MyFormName!MyControlSource! for the criteria, does that go into the criteria box in the query design??

Yess that's right.

I am using that code within a >= and <= to create a date range. When I enter the perameters it doesnt carry to the query. Is there something Im missing??

Can you post the sql of your query.

 

[chris_jolly]

Thanks NeilEg. I actually figured everything out.

I needed to use a BETWEEN AND code in the criteria.

For the report I altered the WHERECONDITION by dimming the start and end dates and calling those for the report.

Thanks for your help