How do I set up a parameter query using a combo box in Access?

[Ryan]

How do I use the value in a combo or list box limit a query or report query?
I have tried two options with no success. They are as follows:

Option 1: Set up a query with the criteria pointing to the combo or list box
value. The criteria reads =[Forms]![Form1]![Combo0]
Option 2: Set up a macro opening a report with the "Where" condition set to
limit the result by the combo or list box value. The "Where" condition reads
[HouseAddress]=[Forms]![Form1]![Combo0]

Each time the report/query comes back blank.

 

[John Vinson]

How do I use the value in a combo or list box limit a query or report query?
I have tried two options with no success. They are as follows:

Option 1: Set up a query with the criteria pointing to the combo or list box
value. The criteria reads =[Forms]![Form1]![Combo0]
Option 2: Set up a macro opening a report with the "Where" condition set to
limit the result by the combo or list box value. The "Where" condition reads
[HouseAddress]=[Forms]![Form1]![Combo0]

Each time the report/query comes back blank.

Your option 1 works fine - *if* Form1 is open at the time you run the
query.

One way to ensure that the form is open is to open the form *first*,
and put a Command Button on Form1 to open the Report (or the Form
displaying the results of the query onscreen). The user need never see
the Query or the Report window, just Form1.

Another approach is to open Form1 in Dialog mode in the Report's Open
event; put a command button "Show" on Form1 which sets Form1's Visible
property to False. Opening a form in dialog causes code execution to
stop (i.e. the report doesn't finish opening); making the form
invisible or closing it resumes code execution. If you do this be sure
to close Form1 in the Report's Close event.

John W. Vinson[MVP]

 

[Allen Browne]

Ryan, the idea is correct, so there must be a problem with the
implementation.

In Option 1, you placed this criteria under what field?
Open its table in design view. What is the Data type of this field?
Number? Text? Date/Time? ...?

In form design view, right-click the combo and choose Properties.
On the Data tab of the Properties box, what is the Row Source?
Which is the Bound Column?

If the Bound Column is 1, then the combo's value will be the 1st field named
in the RowSource. Is this field the same data type as the column in the
query? Do the values match?

This is particularly confusing for people where you actually see the values
in your table/query as a combo. For more info about that see:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm

 

[Duane Hookom]

Is the form open with a legitimate value in the combo box? Are you sure the
combo box bound value is what you expect?

You can find out the actual value of the combo box by opening the debug
window (Press Ctrl+G) and enter:
?[Forms]![Form1]![Combo0]
Do you see the expected value when you press enter to get the value?

 

[John Spencer]

Regarding Option 1.

In addition to what others have posted, it could be that Access is not
interpreting the parameter correctly. Have you defined the parameter type?
It is not required, but

 

[John Spencer]

Dang! Hit the wrong key.

As I said setting the parameter type is not required, but it is often
helpful - especially with dates. Access will sometimes see 12/12/2005 as a
math statement and do the division which yields a datetime on December 30,
1899. Then it will use this calculated value to search the date field.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1 -
[Forms]![Form1]![Combo0]
Select the data type of the parameter in column 2