Can a drop down list be used to set/select query criteria?

[ACSeval]

Hi all. I am a relatively novice user of Access 97 essentially figuring it out as I go, and do most of my work in the Design view (as I am unfamiliar with SQL). I have setup a report that brings information together out of a table (that is regularly updated with a form) and groups the information for our productivity purposes, but I need to limit what is printed in the report to a single criteria (to be set each time the report is run).

The criteria are a set of list names which are rather long and some are elaborate (for example, "ACS South Dakota - 6 Month LONG&quot, and setting parameters [Please enter list name (whatever)] each time has become a problem as too many people are entering incorrect spellings, etc., causing no data to be returned in their report. Currently, to help escape this issue, I have them enter keywords by setting the criteria with a LIKE statement,
(Like "*" & [Enter Keywords from the List Name] & "*&quot
which helps but still has severe limitations.

I would like to set it up such that when the report draws its information from the query, it will ask in a dialog box "which list?" and allow the user to choose from a (drop down style) list of names (which I already have stored in a Table for other parts of the program). Can criteria be selected limiting the user's choice to a predetermined set of information?

If my idea is not a possiblitity based on my limited design-view training, perhaps you could offer some ideas that may accomplish the same goal.

Thanks.
ACSeval -

 

[KornGeek]

You can set up the combo boxes on a form and then have the query get the criteria from the form. Not only does this do what you want, but I think it looks better too.

Make sure you handle Null values. I recommend looking into the Nz function to help with this.

 

[jrbarnett]

I would take a look at this FAQ I wrote as it goes into some detail about how to create a report selection form:

http://www.tek-tips.com/faqs.cfm?spid=702&newpid=702&sfid=3619

John

 

[ACSeval]

Thanks to both of you. It appears that a combo box is exactly what I need. I had never worked with these, but have since created the drop down I need with a form containing a combo box, which links to my table of names.

However, now I am confused about how to set this as my criteria. I put a statement in my Query criteria line that looks like this:

[Forms]![Call List Selection Form]![List Name]

...however, when I try testing the query, a dialog box pops up asking me to Enter Parameter Value of that statement. I thought it should open the form and allow me to choose what I want from the drop down list there.

Is there something else that needs to be changed?Maybe in properties somewhere? Or, add a statement in my form, etc.?

I've been doing a lot of searching to try to find out, but to no avail. (please remember that I am not familiar with code builder or SQL or VBA, etc...that's my next challenge)

Thanks for any ideas.
Sean (ACSeval)

 

[KornGeek]

What you want to do is have the form open before you run the query. For instance, wherever you currently run the query, instead have it open the form. You could then add a Search button (or something like that) to your form and use this button to run the query.