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.
#HOW TO USE MICROSOFT ACCESS CODE#
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. Under the Subj_code field you would enter the following criterion: 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. 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. 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. If you entered “ENGL” at the prompt, you would see the following output:Īs reflected above, only English courses offered in the summer appear. Running this query, you would see a pop-up box corresponding to the criterion you entered under the Subj_code field. Each occurrence of ? corresponds to one and only one character. 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). Also, this query has a criterion under the Term field that will return all matching records for the summer term. For example, the following query prompts you to enter a subject code, as specified in the criteria. In that case, you can use brackets to specify a user-entry criteria field. 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. 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. If you code the criteria on separate lines in the query, they function as an OR condition. Note that the criterion under the field Schd_desc could also be coded as “Lecture” Or “Lab.” Including the * wildcard character both before and after the M and W ensures that you get TWF as well as just W. 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. 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). The other criteria use the wildcard character (“*”) to select records based on additional criteria. Under the Subj_code field, include the criteria “PHYS” to select all sections with a Physics subject code. You could modify the query using the following criteria to obtain this result. 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. This is where the criteria field comes in handy. This might be useful for some purposes, but, generally, you probably want to select a particular group of courses to address a specific question. When running this query you would get something that looks like this:Īs it stands, the query pulls every course offered for the past several years (almost 9,000 records). Microsoft Word quotation marks do not always work in Access.)Īs an example, see the following Access query that pulls basic course data from a data warehouse created from Banner. ( 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.
This tip provides an overview of the criteria field in Access queries, including Flexible Criteria, Date Criteria, and Using Tables as Input. 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.
0 kommentar(er)
