Designing a query with a parameter and a lookup field???

[eggs3]

I'm designing a query. I want to include the field "Location". "Location" in the table I'm quering (is that a word???) is a look-up field, so that my people can just click on their choice from a list of locations.

I want to have a parameter in the query so that they can specify which location is to be used in the query. But when I drag "Location" from the field list into the query design and put the following in the criteria section it doesn't work: Like [Enter location]&"*". When I run the query and type in a location at the parameter prompt I get no records.

I'm wondering if, since "location" is a lookup field in my table, the parameter will not recognize a "typed in" location. Do I have to enter a different phrase in the criteria to perhaps access the same lookup list that appears in my lookup field in the table?

Any help would be greatly appreciated.

 

[Carol]

Just use [Enter Location]and you should get results. You are using like with a location and a wildcard. Unless the criteria you are asking for is present in all instances, you will receive no records.

 

[eggs3]

When I try doing [Enter locations] and I type in a location as it is listed in my lookup table, I get an error saying it is typed incorrectly or is too complex. All I'm entering is the code "san" or "bayou" as they are listed in my lookup table.

Any other suggestions?

 

[Pat Hartman]

Microsoft out-slicked itself when they added this ability to link a table column directly to it's lookup table and show it as a combobox. I never use it because it causes subtle problems such as you are experiencing. You actually have to type the bound value (which is probably a numeric code) to satisfy the parameter prompt. So if you have a lookup table -
1 Apple
2 Orange
3 Pear
You need to type a 3 at the prompt not "Pear". Since that obviously isn't what you want to do, create a form so that you can have a combobox on the form. Then change the query to refer to the combobox field on the form to get it's selection criteria. Run the report from a button on the form. The combo on the form will show "Pear" but its value will actually be 3 which is what the query needs.

Confused?

 

[hjbinn00]

When using a combo box on a form do I chose - get info from a table or query, type in the values I want, or find record on form, based on the value I selected?

Then once I go through the other wizard section do i have it remeber the value or do I store it in a location_____? I have a report I want to be able to run and I don't want to have to make a list of numbers and end up with a report I don't understand.

 

[bparkinson]

Using the pear example above, say you table is storing the ID (3) of the pear in a column named FruitID. You'd have to populate the parameter with the numeric (3) to return that row.

You could denormalize the relationship by creating a query that contained the literal 'Pear' values and the parameterizing that column. That's actually a pretty good use of queries (or views).