Filter Records using Multiple Criteria in Access

There are three logical operators that you are likely to need as you create criteria to filter your data:

1. And,
2. Or, and
3. Not.

In most cases, you do not have to type the operator, since Access combines the criteria with an operator depending on where you put them. For instance, when you use Filter by Form there is a separate tab for criteria that you want to be preceded by Or, whereas criteria you put on one tab are automatically joined by And.
So what exactly do these operators do? Well, Not is easy--it tells Access what you do not want to see. And and Or can get a little confusing. Here is how to think of them: And tells Access that a record must meet both criteria in order to be displayed. For instance, you want to see clients with Casa Grande And Arizona in their records. Or, on the other hand, is a looser requirement, it tells Access that you want to see records with either of your criteria. A request for Austin Or Arizona would produce more records: clients in Austin as well as those in Arizona.

What is the best way to build a form that allows a user to query data through a series of selections? In other words, how can you use a series of controls on a form to create a SQL statement without forcing the user to understand the nuts and bolts of the SQL language

Query construction forms are a great way to let users build queries and select criteria in a paradigm they understand. Of course, the structure of such a form is dependent on the actual schema of the database in question. That is, the fields from which users will make selections are dependent on the database and the business case of the data. Figure 2.51 shows two related tables. Each customer can have multiple purchases. A form, shown in Figure 2.52, has been designed that lets users filter records based on state, customer type, and total amount spent. The custom query form has been designed to allow the user to select any mix of multiple states and multiple customer types, and to specify a minimum amount spent. All of the criteria are optional. If no selections are made, all records are returned. Each time the Go button is clicked, the query SQL is assembled, and the results are put in a new table. The user is required to enter the new table name on the form; if he does not provide a value, he will be prompted to do so.