lookup table in select query

[Mark M]

I am trying to build a select query to select records based on the field
QUARTER. QUARTER is a text field in table med error and is entered in the
table as a lookup field using data from the table QUARTER INPUT. The table
QUARTER INPUT only has two fields, QUARTER (the index key), and Quarter
Description. The QUARTER is input as 0704 for instance (YYQQ). When entering
"0704" in the criteria field, nothing is found in the query. If I put in Like
"1" or Like "2", then data for a particular quarter appears. I thought that
the lookup field would store the index key, which is the QUARTER field, but
it looks like it is using 1,2,3, etc.

Any ideas?

 

[John W. Vinson]

I am trying to build a select query to select records based on the field
QUARTER. QUARTER is a text field in table med error and is entered in the
table as a lookup field using data from the table QUARTER INPUT. The table
QUARTER INPUT only has two fields, QUARTER (the index key), and Quarter
Description. The QUARTER is input as 0704 for instance (YYQQ). When entering
"0704" in the criteria field, nothing is found in the query. If I put in Like
"1" or Like "2", then data for a particular quarter appears. I thought that
the lookup field would store the index key, which is the QUARTER field, but
it looks like it is using 1,2,3, etc.

Any ideas?

You are yet another victim of Microsoft's misdesigned, misleading, obnoxious
Lookup Wizard misfeature. See
http://www.mvps.org/access/lookupfields.htm
for a critique.

The point is that your table APPEARS to contain 0704. That's what you *see*.
But that is NOT what is in your table! The actual content of the table is the
numeric ID; that simple fact is concealed from your view by the Lookup.

Rather than searching the table, create a Query joining your table to the
QUARTER INPUT table. *That's* where the 0704 resides.

 

[Mark M]

Thanks for the help. I just figured out the fix and came back to post this
when I saw your fix. What I did to fix the problem was to eliminate the
lookup in the table and only used it in the form. Seems to work. Thanks for
your help.