drop down list in query

[dawn]

How can a drop down/combo list be used for the
parameter/criteria in a query instead of "Like"
expression. It can be difficult for co-workers to know
how customer names are entered, i.e. GM or General
Motors. Using a list would eliminate a lot of guess work
for the users.

Dawn

 

[Dale Fye]

Dawn,

Cannot do it within the query, but you can create a form that contains
the drop-down list (or a listbox).

Then add a command button to run the query or open a report/form and
based upon the SQL of the query on the form. Something like:

SELECT *
FROM yourTable
WHERE yourTable.CustID = Forms!CustomerReport!cboCustomer


--
HTH

Dale Fye


How can a drop down/combo list be used for the
parameter/criteria in a query instead of "Like"
expression. It can be difficult for co-workers to know
how customer names are entered, i.e. GM or General
Motors. Using a list would eliminate a lot of guess work
for the users.

Dawn

 

[John Vinson]

How can a drop down/combo list be used for the
parameter/criteria in a query instead of "Like"
expression. It can be difficult for co-workers to know
how customer names are entered, i.e. GM or General
Motors. Using a list would eliminate a lot of guess work
for the users.

Create a small Form - let's call it frmCrit. On this Form put a Combo
Box, cboCustomer, with the CustomerID (you *do* have a unique
CustomerID, I hope?) as the bound column, showing the customer name.

Now in your Query (in the query design grid, you don't need to know
SQL for this) put

=[Forms]![frmCrit]![cboCustomer]

on the Criteria line.

It's convenient to base a Form (for onscreen use) or a Report (for
printing) on the query, and put a command button on frmCrit to launch
the form or report. The toolbox wizard will build the button code for
you.

 

[dawn]

I followed your suggestion, however I get a pop up box
that requests Enter Parameter Value "Forms!frmCompany list!
cboCustomer". Where am I going wrong. This is the same
message I received when trying a FROM WHERE SQL
statement. My form (Company list) with combo box is based
on my customer table that has a company ID and company
name.

Dawn

-----Original Message-----

How can a drop down/combo list be used for the
parameter/criteria in a query instead of "Like"
expression. It can be difficult for co-workers to know
how customer names are entered, i.e. GM or General
Motors. Using a list would eliminate a lot of guess work
for the users.

Create a small Form - let's call it frmCrit. On this Form put a Combo
Box, cboCustomer, with the CustomerID (you *do* have a unique
CustomerID, I hope?) as the bound column, showing the customer name.

Now in your Query (in the query design grid, you don't need to know
SQL for this) put

=[Forms]![frmCrit]![cboCustomer]

on the Criteria line.

It's convenient to base a Form (for onscreen use) or a Report (for
printing) on the query, and put a command button on frmCrit to launch
the form or report. The toolbox wizard will build the button code for
you.


.