LookUp Field

[Guest]

The table on which the query is based includes LookUp Table fields. I want
to set specific criteria in my query on these fields. When I enter the data
it returns error messages. I can use specific criteria in other fields, just
not on the LookUp fields. Is there a way around this???
Thanks!

 

[Jeff Boyce]

Sam

Yes. Don't use Lookup data types in tables <g>!

Why not? You've just described the primary reason folks in the newsgroups
generally advise against using them ... what is stored and what is displayed
don't match.

Makes coming up with queries harder, right?!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Not necessarily. I have maybe 15 to 20 names to choose from and having to
type those in each time and risk making a typo, etc. makes using the lookup
table in my table worth it. I also have as many account numbers, so when
entering data into my form I can simply select the data. Do you know of
another way around this?

 

[John Vinson]

The table on which the query is based includes LookUp Table fields. I want
to set specific criteria in my query on these fields. When I enter the data
it returns error messages. I can use specific criteria in other fields, just
not on the LookUp fields. Is there a way around this???
Thanks!

Yes. Don't use Lookup Fields in your tables AT ALL. This is one of
many reasons to avoid them!

The field APPEARS to contain a text value (the looked-up text). It
doesn't. It contains a concealed number; if you want criteria to be
applied, you must pass the number as the criterion, not the text value
(and, of course, the number is hard to find and even harder to
remember, because it's concealed from your view).

Instead, JOIN the lookup tables to your main table in the query;
select the text fields from the joined tables; and apply the criteria
to these fields.

John W. Vinson[MVP]

 

[John Vinson]

Not necessarily. I have maybe 15 to 20 names to choose from and having to
type those in each time and risk making a typo, etc. makes using the lookup
table in my table worth it. I also have as many account numbers, so when
entering data into my form I can simply select the data. Do you know of
another way around this?

Yes. Don't use Tables for *ANYTHING* other than data storage (and
occasional debugging).

Use Forms to enter your data - and *do* put Lookups (combo boxes) on
the Forms.

It's not a matter of avoiding the lookup capability, that's essential;
it's a matter of using lookups *in their proper place*, data entry and
editing Forms rather than Tables.

John W. Vinson[MVP]