Query with filter

[Cesar Urquidi]

I made a query with a filter from my "Equipment" table.
I typed "[Enter Device Category:]" in the "Device Category" field as the
criteria, but the "Device Category" field happens to be a look-up field. In
other words; I have a table called "Device Categories" which feeds the
"Device Category" field in my new query.
When I open the query and enter the device category I want to filter, the
query doesn't display anything, because the "Device Categories" table that
feeds the "Device Category" field in my new query has two columns:

ID Device Category
1 ANALYZERS
2 CALIPERS
3 MICROSCOPES

When I enter the number of the "Device Category" (in this case "1"), the
query filters ok., but I want to be able to enter the words (in this case
"ANALIZERS").

Can somebody help me solve this problem???

 

[Jeff Boyce]

Cesar

I'm going to guess that you've used the "lookup" datatype in a table. Even
though that table displays "ANALYZERS", it is actually storing "1" in the
field.

That's why your query with "1" as a selection criterion is returning the
"ANALYZERS".

Confused yet?!

The "lookup" datatype field is the source of considerable opinion ... mostly
negative ... in the tablesdbdesign newsgroup. You've discovered one of the
reasons why this is so.

The solution is to stop using the table to do "lookup". Instead, use a
combobox in a form to do the lookup. Change the datatype in your table to
whatever the underlying ID field is (Long, Integer, whatever).

Now you can make a query that joins the main table to the "lookup table" on
that ID field both share, and use the text portion of the lookup table as
the field for which the prompt seeks records.

But wait! There's more!

Instead of forcing users to "know" what the text (or the IDs) are, why not
use a query to feed the combobox in the form. That way, the user can look
at the list of possible values and select one.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP