using criteria in field that has a look up table

[AEA]

I have a field that uses a lookup table to generate the
data inside the field. When I use a query with criteria
such as "dentist" I get a "data type mismatch in criteria
expression" error because ID (which is a number) is the
bound column in the lookup table. Is there any way to set
up a query that uses criteria to get the information in
the second column of the lookup table?

e.g.
field name: [referred to] which is populated by a lookup
table called referrals:

ID referral
1 Doctor
2 Dentist

Thanks for the help

 

[Duane Hookom]

Many of us recommend against using lookup fields. You can add the lookup
table to your query so that you can place the Referral field in the query
grid.

 

[AEA]

Thanks for you reply. What is the problem with using
lookup tables?

-----Original Message-----
Many of us recommend against using lookup fields. You can add the lookup
table to your query so that you can place the Referral field in the query
grid.

--
Duane Hookom
MS Access MVP

I have a field that uses a lookup table to generate the
data inside the field. When I use a query with criteria
such as "dentist" I get a "data type mismatch in criteria
expression" error because ID (which is a number) is the
bound column in the lookup table. Is there any way to set
up a query that uses criteria to get the information in
the second column of the lookup table?

e.g.
field name: [referred to] which is populated by a lookup
table called referrals:

ID referral
1 Doctor
2 Dentist

Thanks for the help

.

 

[Rick Brandt]

Thanks for you reply. What is the problem with using
lookup tables?

There's no problem with lookup *tables*. It is lookup *fields* that are
a crappy idea.