Multiple Parameter fields

[BMeek]

I want to have two fields prompted to the user for an optional filtering of data. When I use the format for one field, everything is OK. But when I try to add a second field using the same technique, I get blank results unless put a value in each of the parameter prompts. How can I use the parameter prompt with both fields?

Here is the answer to my question for one field from Microsoft Access Help:

I want the option of returning all records with a parameter query.
In the design grid under the field that contains the prompt in the Criteria cell, type [prompt] Is Null in the Or cell, where prompt is the same prompt that is in the Criteria cell for that field. For example, in the CategoryID field:

In this cell Type
Criteria Like [Enter Category ID:]
Or [Enter Category ID:] Is Null


Now when you run the query, if you leave the Enter Parameter Value dialog box blank, the query returns all records.

If I try to get a prompt on Units and Volume, I have to make an entry into BOTH prompts or my results are squirrelly? I prefer not having to make my users enter 1 or 0 into the prompts.



Bryan Meek
bmeek@adelphia.net

 

[lespaul]

Then what you need to do is create your own parameter form instead of using the [Enter criteria here] method.

You will have to change your query to:

SELECT * FROM TABLE WHERE CRITERIA = Forms!FormName!CriteriaItem

for each of the items you add to the form. You'll probably have to use an IIF or NZ to check for a null entry.

HTH

Leslie

 

[BMeek]

Thanks for the suggestion Leslie. I am using a form that allows the user to select a report based on results with a variety of parameter prompts. The action is behind a button that calls a report based on the query. For example:

Agent Results by Office Location
Agent Results by Property Geography
Agent Results by MLS-wide

In addition to Agent LastName, FirstName, each report then has a series of prompts for Office Location or Prop Geo then by the two numeric values "Units" and "Volume". All the other prompts are handled in subqueries and union queries that build the dataset used for the final report. The numberic prompts are the last ones in the 'Master' query.

I would prefer not having another form pop up for the user to input or select a value. Do you think I will need to place the prompts in the subqueries or is there another way?

Bryan Meek
bmeek@adelphia.net

 

[lespaul]

Sounds like parameters in the sub-queries may be the way to go. But if you are having users enter/type the parameters they may not get any results if it's spelled incorrectly, where if you have a form where they can enter all the parameters at once, you can control what they select with a combo box.

Just my 2¢!

Leslie