Using the Criteria Field in Microsoft Access

Many of us use Microsoft Access to extract data from Banner or other enterprise systems, or to store data that doesn’t fit in our enterprise system. This tip provides an overview of the criteria field in Access queries, including Flexible Criteria, Date Criteria, and Using Tables as Input.

(Note: If you try to copy the criteria fields shown below directly to Access, you may get an extra set of quotation marks that will need to be deleted. Microsoft Word quotation marks do not always work in Access.)

As an example, see the following Access query that pulls basic course data from a data warehouse created from Banner.

Figure 1

When running this query you would get something that looks like this:



As it stands, the query pulls every course offered for the past several years (almost 9,000 records). This might be useful for some purposes, but, generally, you probably want to select a particular group of courses to address a specific question. This is where the criteria field comes in handy.

Let’s say you are only interested in Physics lecture or lab sections that meet on Monday and/or Wednesday for calendar year 2013-14. You could modify the query using the following criteria to obtain this result.

Figure 3

Under the Subj_code field, include the criteria “PHYS” to select all sections with a Physics subject code. The other criteria use the wildcard character (“*”) to select records based on additional criteria. For example, under the Term field you would use the criteria Like “2014*” to select all records with a term code of 201420, 201430, and 201430 (corresponding to summer, fall, and spring terms in the 2013-14 academic year). Under the Days field you would use the slightly more complex criteria Like “*M*” or Like “*W*” to pick up any record with M or W anywhere in the field. Including the * wildcard character both before and after the M and W ensures that you get TWF as well as just W. Note that the criterion under the field Schd_desc could also be coded as “Lecture” Or “Lab.”

If you code the criteria on separate lines in the query, they function as an OR condition. For example, the following query pulls all sections with a subject code of Physics ­or with a class type of “Lecture,” so you might have a Physics Lab or a Biology Lecture returned, among many other possibilities.

Figure 4

Now, let’s say you want to be able to select the subject code whenever you run the query, without having to hard code it into the criteria field. In that case, you can use brackets [] to specify a user-entry criteria field. For example, the following query prompts you to enter a subject code, as specified in the criteria [Enter subject code:]. Also, this query has a criterion under the Term field that will return all matching records for the summer term. When using the ? wildcard character to select the summer term; this criteria field pulls 201220, 201320, 201420, and so on (the 20 suffix indicates a summer term). Each occurrence of ? corresponds to one and only one character.

 

Figure 5

Running this query, you would see a pop-up box corresponding to the criterion you entered under the Subj_code field.

Figure 6

If you entered “ENGL” at the prompt, you would see the following output:

Figure 7

As reflected above, only English courses offered in the summer appear.

Using Flexible Criteria

Using the output from the original query (Figure 1 and 2 from the beginning of this article), a simple criteria expression in the Subj_code field allows you to select just those records matching the criteria (e.g. “MATH”). You could also use the wildcard character (“*”) to select a range of values; for example, if you enter “2*” in the criteria field for Crs_num you pick up only 200-level courses.
This can be quite useful, but what if you want the option of including all subject codes, in addition to being able to select specific subject codes? This gets a little bit trickier, but it can be done using the following syntax in the criteria field.

 

Figure 8

Under the Subj_code field you would enter the following criterion:

Like "*" & [Enter subject or blank for all: ] & "*"

If you enter a specific subject code at the prompt, say MATH, the criteria field is parsed as *MATH*, which would give you what you want – in this case the wildcard characters won’t affect the criteria. But if you don’t enter anything at the prompt, the criteria field is parsed as **, which is just the wildcard character, meaning that all records are returned.

Note that you would include an additional criterion under the Actual_enroll field, as follows:

 >=IIf([Include sections less than 20? ]="Yes",0,20)

Here, you would use the “IIf” keyword, which is used for an if-then statement. The basic syntax is iif(condition, then a, else b). In this example, this criterion generates a second prompt: “Include sections less than 20?” If you enter “Yes” then the criteria is parsed as >=0 and all records are included. If you enter anything else (e.g. “No”) then the criteria is parsed as >=20 and only sections with an enrollment of 20 or more are included.

So, if you ran the query and entered “ENGL” as the first prompt and “No” at the second prompt, the output would look something like this:

 

Figure 9

As you can see, results include only English courses with 20 or more students.

Using Date Criteria

  Using the output from the original query (Figure 1 and 2 from the beginning of this article), you can code just a single criteria in the Start_date field to select records with a specific value. For example, if you specify #9/8/15# the query only returns records with a start date of September 8, 2015.
 
But date field criteria can be more dynamic than that. For example, if you want to select all sections with a start date in June, you could use the following criteria field.

 

Figure 10

In this example, you would include the following criterion under the Start_date field:

DatePart("m",[start_date])=6

This criterion extracts the month from Start_date and evaluates it to see if it is equal to 6 (i.e. June). Similarly, if you wanted to extract all records with a start date in calendar year 2012, you could use the following syntax:

DatePart("yyyy",[start_date])=2012

As another example, let’s say you want to extract all sections that were offered in the past two years. You could use the following criterion under the Start_date field:

>DateAdd("yyyy",-2,Date())

This criteria subtracts the current date, coded as Date(), from the year of the Start_date field, and determines if the difference is less than 2.

Using Tables as Input

Let’s say you have the following very simple table, with just one record and three fields, which might be used as the base table for an input form:

Figure 11

After you enter the appropriate values into the input form, you could then run a query based on the values entered into the form. The query structure might look like this:

Figure 12

Note that each criteria field references the corresponding field in the base table; note also that the two tables are not linked. Running this query would display the following output:

Figure 13

Only Biology lecture sections offered in term 201530 are included, as specified by the values in the base table.

There are also more advanced ways to use the criteria field in Access, but these tips provide some basic applications that will hopefully prove useful to Access users.