Using Criteria and a Drop down Box

[Guest]

I have a report which accesses a query that asks the users for a range in
the criteria. For the criteria they would need to input a low number and
then a high number. They're numbers are set as follows:

201-250
251-300
etc

Is there a way to have a drop down or list box when the parameter boxes pop
up so they don't have to "know" the specific numbers. They way the db was
set up these numbers won't be remembered by these users.

Thanks. Hope this makes sense

 

[Jeff Boyce]

Not within a query...

But there is a way to do this. Create a form that has one (or two) combo
boxes (or list boxes) with the range information. I'm guessing two boxes,
one for low, one for high.

Modify your query to look at those combo boxes on the form to get the
selection criteria. Your syntax will vary, but might be something like the
following in the Criterion "cell":

Between Forms!YourForm!YourLowComboBox And
Forms!YourForm!YourHighComboBox

NOTE: this approach only works if the form is open, and if suitable values
are in the combo boxes.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Reading the post I get the impression that the field has the 'low' and 'high'
number separated by a dash. I this is correct then you need to do something
different.

If they would only pick a preset range that exactly matches the data entry
then build the form but use this as criteria.
Forms!YourForm!YourLowComboBox

If they would pick more than one set of ranges then the form need two
comboboxes. Have the 'low' one source the Left([YourRangeField],3) and the
'high' source the Right([YourRangeField],3).
In the query have two output fields like Low_Group: Left([YourRangeField],3)
and High_Group: Right([YourRangeField],3).
For criteria use

= Forms!YourForm!YourLowComboBox for the Low_Group and

<= Forms!YourForm!YourHighComboBox for the High_Group