Use a Table to lookup information in a query

[mous]

I have a query that uses information from a form to return a data set.

In this query I also want to exclude certain codes i.e. CSC01, CSC02 and have therefore typed, not like CSC01 and not like CSC02. This works.

I have explicitly told the query these codes.

I have these codes in a table and could be subject to change. Can I not have the query lookup the information in the table to say if the codes in the data set are not matching those in the table, do not return any records.

Thanks

 

[Mile-O]

In this query I also want to exclude certain codes i.e. CSC01, CSC02 and have therefore typed, not like CSC01 and not like CSC02. This works.

There's no need to use Not Like "CSC01" And Not Like "CSC02" as a criteria as:

• The Like operator is used for comparisons where you only have part of the text. With codes such as CSC01 and CSC02 you have the full code and therefore should use the not equals to symbol which is <>.
• If you want to lookup up multiple things and restrict a few options then you are better to use the IN operator. Not In ("CSC01", "CSC02")
• To make this query dymamic you will need to build the SQL in code and a QueryDef object. I've done examples of this before (that's why we have a Search function on the forum) so I'm not going to repeat it again. You will find this helpful though (from Microsoft's Knowledge Base website).