A prompt with multiple criteria for displaying a report...

[Eltoque]

Hi. I am a newbie. I'm not sure if this an easy question or if I have taken an unintended leap into the extreme:

What I have is an Access file which consists of only one table. We want to be able to generte reports from this data but we would like to put in perhaps a dialog box that would prompt for multiple perameters.
For example:
The fields we have are Manager, worker, date, resolution, etc.
I would like to generate a report that could show "all" or specify a manager and a specific worker for a certain time period. Very specific. There are too many versions of the report being requested and we have run out of room to place a "basic" report and I need to keep it user friendly. I figured a perameter prompt would be the best solution but I cannot get a query to work right. I have gotten a macro to get the box to pop up but it does not read when I place the criteria in. I cannot find any specific programming language to work with to give me an idea of how to get this to work. I would appreciate any help or suggestions. Thank you.

 

[jaydlewis]

since you have such a simple database with one table, you might just bind your report to a query based on the one table that users could just filter in datasheet view before opening the report

 

[RickSpr]

There is a way you can use query parameters. I assume you tried setting up a query with, for example, the Criteria entry under Manager set to some parameter prompt, such as "[Enter Manager]". If you change that to "LIKE [Enter Manager]", then when the prompt is displayed, you can either enter a manager name, or you can enter "*" for all managers. This is not perfectly user friendly (I'm sure they'd rather have a list box, with some kind of "(All)" entry at the top), but it's not too hard for users to learn.

To get truly user friendly, with a list or combo box for example, you'd have to write VBA code that executes in the report's Load or Open event procedure. The code would open a modal form in which the user selects or types the parameter values. When they click the OK button on the form, the VBA code in the report gets the parameter values from the form, closes it, and then builds a SQL criteria string, which it assigns to the Filter property of the report. There is also code in the form that generates the contents of the list or combo boxes. This is all a bit complicated, and it sounds like it might be more of an undertaking than you're up for, but if you want to try it, feel free to ask for help. Rick Sprague

 

[UncleJack]

Create a form and place combo boxes for parameter choices and text boxes for date parameters. For example, use a combo box named "PickManager" that queries the table for managers. The user simple selects the manager of choice. Other fields would be handled in the same way. User selects as many parameters as desired and clicks a button to launch the report.

The report is based on a single query that has the form's combo boxes referenced on the criteria line of the query. For example, in the criteria line for the managers field put "Forms!FormName!PickManager or is null" as the criteria. If a manager name is specified on the form then it will be used in the query. If none is specified then all managers will be included.

For time periods use < or > to limit the time to before or after the specified date. If you use &quot;From&quot; and &quot;To&quot; date fields on the form then use &quot;Between Forms!FormName!From and Forms!FormName!To&quot; as the query criteria.

This way you have a single query that can be tailored at run time to many different configurations.

Naturally, you'll want to include some bells and whistles on the form such as a &quot;Clear&quot; button to blank the combo boxes. Perhaps date verification routines that trap dates out of range for the dates in the table (Check the DMax and DMin Functions).

Uncle Jack

 

[Eltoque]

Thank you all very much. I will give your suggestions a go and let you know what happens. I understand queries and such but I have yet to get to a level where I can really make them work for me. But I am working on it!
Thanks again!