lookup in a query

[easypray]

I searched previous threads but cant make too much sense of this.

I know this is easy I have done it before but I am VERY rusty right now....

I have a table field that is a look up field in the table or form. When that feild is used in a Query it is grabbing all records.

How do I get the criteria in the Query to prompt the user to pick one of the list from the lookup list in the table?

not [Please type in criteria] but rather select one from this list

Did that make sense?

TIA

 

[mp9]

You can't get the parameter pop-up you get when the query runs to display a combo-box. Instead, you'll have to create a form with the combo-box and an "OK" button on it. Use this combo to select your parameter from a list, then use the OK button to pass the selected value to your query.

http://www22.brinkster.com/accessory/

 

[easypray]

I am with you right up until the last part of "pass the selected value to your query"

I am not familiar with how to do that, I usually design all the querys for the user and have them choose a report I have never created a "create a custom" report before - but that totally sounds like my solution here...thanks for the reply...and details on that last step would be greatly appreciated.

 

[juschuma]

Try this...make sure the report already runs fine without your where statement. You're just adding that part in here. Put this code (after replacing the appropriate values) into your command button click event on the form.

Dim stDocName As String, sqlwhere As String

sqlwhere = ""
stDocName = "Your_Report"

'Build your where statment

sqlwhere = "YourTable.YourField = " & Me.YourComboBox

DoCmd.OpenReport stDocName, acViewPreview, , sqlwhere

 

[JEShannon]

On the form from which you run the report, have the user fill out the appropriate parameters (select from your combobox).
Then open up the query behind the report, go to the field you want to add criteria to and, in the criteria field put in something like this (referencing the form containing the drop down box):
"Between [Forms]![yReport Selection Screen]![From Date] And [Forms]![yReport Selection Screen]![To Date]"

Only you'd use your form name "[Forms]![your form name]![your field name]"

As MP9 pointed out: you either use a parameter query or a form (previously filled out) to provide the criteria.

One middle ground is to include a limited number of choices in the parameter criteria. Like:
"[Payment TypeCash,Check,CCard) ]"

Good luck.
Jim