Selecting criteria in Queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table from which I need to extract certain information from a
specific field, the field has 2 unique criteria "int" and "ext".
I have already created the query and when it's run, the query gives me all
the records (both int and ext).

I now need to 'ask' the user of the query (via the Enter Parameter Value
box?)whether they wish to view all of the records with either "int" or "ext"
or "both". Is there a way in which I can do this please?

Ideally, the user can then select "int" if they want to view all records
that contain int; or they can select "ext" if they want to view all records
that contain "ext"; or they can select "all" if they want to view everything.

I hope this makes sense!

Many thanks in advance.

AW
 
One method:
Assumption: YourField is never blank and it never contains any values other
than Int or Ext

Field: YourField
Criteria: Like IIF( [Enter 'int', 'ext', or 'Both'] = "Both","*", [Enter
'int', 'ext', or 'Both'])

You would be better off if you could use a form to get the criteria. The
form could ensure accuracy of entry. If you use this method, I would have
two columns in the combobox. Column 1 (hidden) would have the values *,
Int, and Ext and you would use this bound column and pass its value to the
query. Column 2 (Visible) would have the values <<Both>>, Int, Ext and you
would show these values to the user.

Check out this article for a detailed discussion.
http://www.fontstuff.com/access/acctut08.htm

A brief quote from an John Vinson (Access MVP) posting.

You'll need to create a small unbound Form (let's call it frmCriteria) with
a Combo Box control (cboCrit) on it. Use the combo box wizard to select the
table for the selections, and be
sure that the bound field of the combo is the value you want to use as a
criterion. Save this form.

Now use

=[Forms]![frmCriteria]![cboCrit]

as the criterion in your Query.

It's convenient to base a second Form or Report on the resulting query to
display the results; if you put a button on frmCriteria to launch that form
or report, the user can enter the criterion and view the results in one
simple operation!

Quoting John Vinson

Keywords: Parameter Queries
 
Back
Top