multiple criteria in access queries

[Guest]

I am trying to build a query that searches a table for all values that meet any one of about 50 criteria
When I copy/paste the list of criteria into the criteria field they all go into one field like one long criteria
How do I enter a list of criteria without typing 'Or' in between each one (which gives me a max number of characters error anyway!)

 

[Nikos Yannacopoulos]

Pete,

If your criteria cannot be gathered somehow from within your database, it's
probably a lot easier to paste your criteria into another table, bring it in
the query design and join the two tables on the field to be filtered.

If, on the other hand, those criteria come from entries in an existing
table, you could use a subquery, e.g.
IN (SELECT Customer_ID FROM tblCustomers WHERE State_ID = "CA")
(this would return all records for customers in California)
which is to be typed in the first criteria line of the field in question.

HTH,
Nikos

I am trying to build a query that searches a table for all values that

meet any one of about 50 criteria.

When I copy/paste the list of criteria into the criteria field they all go

into one field like one long criteria.

How do I enter a list of criteria without typing 'Or' in between each one

(which gives me a max number of characters error anyway!)

 

[John Vinson]

I am trying to build a query that searches a table for all values that meet any one of about 50 criteria.
When I copy/paste the list of criteria into the criteria field they all go into one field like one long criteria.
How do I enter a list of criteria without typing 'Or' in between each one (which gives me a max number of characters error anyway!)

Two suggestions:

1. The IN() query operator will let you logically OR a bunch of
criteria:

WHERE fieldname IN(1, 8, 12, 13, 22, 49, ...)

2. Probably better: create a second table with one field for your
criteria values. JOIN this table to your main table, joining the field
you want to search to the criteria field.