one query/one report/different params

[tweetyksc]

I've got the basic query down finally to pull the correct data for what I need.

What I need now to do is have the user be able to run a report from the query in a variety of different ways, then have what they selected show up in the report header and footer. I know how to pull this data from the user's form into the query/run the report in code, etc. etc. What I'm trying to do is use ONE report for this, and have the report reflect the parameters. They will be selecting the parameters from a form.

For instance, sometimes they will run by sales rep, sometimes by year, sometimes by qtr and year.

I want whatever they choose to appear on the report.
I.e. Commission Report -- 2003
Commission Report-- 1st qtr 2003
In the past, I've done separate reports for this just to get it out
but I know there's a better way. Also, this database will be shared so I want to keep the amount of objects to a minimum.

Also, is there a way that I can use just ONE query so that if
for instance they choose the year they won't get prompted for the qtr, employee, etc. because they're not running it for that.

I'm wondering if this can be done with ONE query/ONE report.
Thanks in advance!

 

[Jack Cowley]

You will find your answer here.

hth,
Jack

 

[tweetyksc]

Thanks, I'll check that out!
I've been using the criteria field in the query pointing to the form, and using VB to run stuff...kind of mixed. This is just what I was looking for, to move forward.

 

[Jack Cowley]

You are welcome. Good luck with your project...

Jack

 

[tweetyksc]

Thanks again...just never came across this as I'm self-taught with Access.

Since I'm using Access 97 I'll have to use those set of instructions.
As I understand it, I'd really keep it the way it is (forms and query criteria), but add "forms!formname!ControlName is null" after pointing to the form field in order to ignore and not prompt if the field is null. That takes care of my first problem very quickly and easily!

Now that I've narrowed down the queries, what about being able to pull what is chosen on the criteria form and put it in the header/footer of the report?

ETA: I think I found the answer in another thread, but because there will be possibly multiple criteria I just have to play around with it and see what works visually, etc.

 

[Jack Cowley]

You can display what is entered on the form in a report as long as the form stays open when you open your report. Referring to a control on the form with code like this:

=[Forms]![FormName]![ControlName]

in the Control Source of a control in your report will show the value the user is using for the criteria.

If you are trying to select more than a couple of parameters in a query then you might want to consider building your query on the fly rather than relying on a saved query with multiple parameters.

Jack