Criteria in access 2010

Criteria in access 2010 DEFAULT

Access 2010: Query Criteria Quick Reference Guide

Lesson 19: Query Criteria Quick Reference Guide

/en/access2010/queries-how-to-create-a-find-duplicates-query/content/

Query criteria quick reference guide

Below, you'll find a guide containing 20 of the most common criteria used in Access queries. While these criteria are all fairly simple, each one can help you carry out meaningful searches of your data. If you find this guide useful, download the printable version so you'll always have it handy. For a more comprehensive guide to criteria, look at Microsoft's official Examples of query criteria.

When entering the criteria, write them exactly as they are written in the second column, replacing x with your search term—or in the case of dates, replacing mm/dd/yyyy with the desired date.

Simple criteria for all data types:

Criteria NameWrite it like...Function
Equals"x"Searches for values equal to x
Does Not EqualNot in ("x")Searches for all values except those equal to x
NullIs NullSearches for empty fields
Not NullIs Not NullSearches for non-empty fields

Simple criteria for text:

Criteria NameWrite it like...Function
ContainsLike "*x*"Searches for all values that contain x
Does Not ContainNot like "*x*"Searches for all values except those that contain x
Begins withLike "x*"Searches for all values beginning with x
Ends withLike "*x"Searches for all values ending with x
Comes After>= "x"Searches for all values that come afterx in alphabetical order.
Comes Before<= "x"Searches for all values that come before x in alphabetical order.

Simple criteria for numbers:

Criteria NameWrite it like...Function
BetweenBetween "x" and "y"Searches for values in the range between x and y
Less Than< xSearches for all values smaller than x
Less Than or Equal To<= xSearches for all valuessmaller than or equal to x
Greater Than> xSearches for all values larger than x
Greater Than or Equal To>= xSearches for all values larger than or equal to x

Simple criteria for dates:

Criteria NameWrite it like...Function
BetweenBetween "#mm/dd/yy#" and "#mm/dd/yy#"Searches for dates that fall between two dates.
Before<#mm/dd/yy#Searches for dates before a certain date
After>#mm/dd/yy#Searches for dates after a certain date.
Today=Date()Searches for all records containing today's date
Days Before Today<=Date()-xSearches for all records containing dates x or more days in the past

/en/access2010/4-free-alternatives-to-microsoft-office/content/

Sours: https://edu.gcfglobal.org/en/access2010/query-criteria-quick-reference-guide/1/
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.

Criteria-Field-in-Access-1

Figure 1

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

Criteria-Field-in-Access-2

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.

Criteria-Field-in-Access-3

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.

Criteria-Field-in-Access-4

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.

Criteria-Field-in-Access-5

Figure 5

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

Criteria-Field-in-Access-10

Figure 6

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

Criteria-Field-in-Access-7

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.

Criteria-Field-in-Access-8

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:

Criteria-Field-in-Access-9

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.

Criteria-Field-in-Access-10

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:

Criteria-Field-in-Access-11

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:

Criteria-Field-in-Access-12

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:

Criteria-Field-in-Access-13

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.
Sours: https://www.airweb.org/article/2016/05/07/using-the-criteria-field-in-microsoft-access
  1. Design your own award
  2. Used cars mandeville
  3. China marks crossed swords

MS Access - Query Criteria



Query criteria helps you to retrieve specific items from an Access database. If an item matches with all the criteria you enter, it appears in the query results. When you want to limit the results of a query based on the values in a field, you use query criteria.

  • A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value.

  • Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

  • To add some criteria to a query, you must open the query in the Design View.

  • You then identify the fields for which you want to specify criteria.

Example

Let’s look at a simple example in which we will use criteria in a query. First open your Access database and then go to the Create tab and click on Query Design.

Query Design

In the Tables tab on Show Table dialog, double-click on the tblEmployees table and then close the dialog box.

Tblemployees

Let us now add some field to the query grid such as EmployeeID, FirstName, LastName, JobTitle and Email as shown in the following screenshot.

Query Grid

Let us now run your query and you will see only these fields as query result.

Query Result

If you want to see only those whose JobTitle are Marketing Coordinator then you will need to add the criteria for that. Let’s go to the Query Design again and in Criteria row of JobTitle enter Marketing Coordinator.

Marketing Coordinator

Let us now run your query again and you will see that only Job title of Marketing Coordinators are retrieved.

Retrieved

If you want to add criteria for multiple fields, just add the criteria in multiple fields. Let us say we want to retrieve data only for “Marketing Coordinator” and “Accounting Assistant”; we can specify the OR row operator as shown in the following screenshot −

Accounting Assistant

Let us now run your query again and you will see the following results.

Run Query

If you need to use the functionality of the AND operator, then you have to specify the other condition in the Criteria row. Let us say we want to retrieve all Accounting Assistants but only those Marketing Coordinator titles with “Pollard” as last name.

And Operator

Let us now run your query again and you will see the following results.

Run Query
Sours: https://www.tutorialspoint.com/ms_access/ms_access_query_criteria.htm

Microsoft Access – Using IF Queries as Search Criteria

Using IF Statements as Query Criteria In Microsoft Access

This article assumes a knowledge of the standard use of the Criteria row in a Microsoft Access Query and the Operators, for example, =, <>, Like, IS that can commonly be used there (see Appendix – Operators at the end of this article for more details). These are covered during our Microsoft Access training courses. Another Access article you might find useful if you are not so familiar with Access is “Using Union Queries / Select Statements In Access and SQL” Click here to find out more.

Note that an IF statement in MS Access is classed as an Inline IF. As such the correct syntax is actually IIF.

When do you need IF in a Criteria?

A common requirement for an IIF statement in a query’s criteria is where multiple selection criteria have been delivered as a collection of controls/”choices” on one dialog box.

 

IIF Statement Dialogue Box in microsoft Access

For example, this dialog box allows the operator to find data based on a wide combination of choices but not all choices will be used at any one time.

It is essential that those criteria/controls/choices not used do not in any way impact on the data that is returned.

To this end, a statement similar to IIF a Job No is not specified select all Job Numbers so that the first criteria picked up in this example is then the Date range, but if a Job No is provided that should be the only record selected.

However, an IIF statement cannot be used in the Criteria area of an Access Query as an Operator is required, for example: =,>=, <>, Like, Between… (see Appendix – Operators) for further examples.

In the absence of such an operator Access automatically, although not visibly, adds = to the front of the statement changing the way it is read to essentially “Field = IIF Statement” which makes no sense as a Field cannot equal an IF statement that is returning a Criteria.

The result of such a query would be no records returned.

The Solution

Using the above example one might naturally think the following would be correct but it is not for the reasons already explained above.

Screenshot showing incorrect Query Setup

The IIF statement has to be set up as a Field in the query. This is where calculations and formulae are normally placed, however, in this case, no field name has to be given. The expression can be written directly into the Field area:

 

MS Access - 3 - Correct IIF Query Setup Example

IIf(IsNull([Forms]![dlgDriverRunSheets]![txtJobNo]),[JobNo] Like “*”,[JobNo]=[Forms]![dlgDriverRunSheets]![txtJobNo])

 

The above IIF statement when broken down provides a “What to do if it’s TRUE” and a “What to do if it’s FALSE” section. It gives full stand-alone criteria for the [Field] Operator Criteria in its own right:

 

TESTIs there nothing (IsNull) in the Job No control of the Driver Run Sheet dialog box in our example.
TRUEIf there is nothing then a full criterion of [Field] Operator Criteria is provided. [JobNo] Like “anything” using the wildcard “*” which will pick up ALL Job Numbers thereby ensuring the absence of a Job Number has no impact on what records are selected.
FALSEOtherwise/else a full criterion of [Field] Operator Criteria is used: [JobNo]=to job number selected on the dialog box. Only records with the same number as the selected Job No will be selected.

 

In this example where no Job No is selected via the dialog box, the IIF statement returns True which sets a criteria to select all job numbers. Where a Job No is provided on the dialog box, valid or otherwise, the result of the IIF statement becomes False which sets the criteria to select only matching job numbers.

For records in the data set being queried which meet whichever of the two criteria has been selected by the action of the IIF statement, a value of -1 is returned. For those records that do not match the selected criteria a 0 which equates to False.

To select only those records that match the selected criteria enter <>False in the Criteria row of the Query.

Note that in some database products True is returned as positive 1 and not negative and in some products, any value other than 0 is also True, but False is always 0 and for this reason, it is safer to use a criterion of <>False rather than True.

As the column in the query is not required visually and is only acting as a filter/criteria untick the tick box in the row marked Show.

In summary, the column in the query with the IIF statement would read as follows:
Field: IIf(IsNull([Forms]![dlgNameOfForm]![ControlOnForm]),[Field] Like”*”,[Field]=[Forms]![ dlgNameOfForm]![ ControlOnForm])
Show: Uncheck
Criteria: <>False

Appendix – Operators

Access criteria require an Operator to appear first. Listed below are standard comparison operators and also the more special criteria specific operators.

For both Comparison and Special Operators a result of True or False is returned as described in the table and when applied as Criteria in an Access Query when True is returned for a record the corresponding record is selected.

The use of these comparison operators is covered during our Access training courses which run in London and Guildford.

 

Comparison Operators

Comparison OperatorsDescription
<Returns True if the first value is less than the second value.
<=Returns True if the first value is less than or equal to the second value. > Returns True if the first value is greater than the second value.
>=Returns True if the first value is greater than or equal to the second value.
=Returns True if the first value is equal to the second value.
<>Returns True if the first value is not equal to the second value. It is also referred to as NOT as this article uses <>False

 

Special Criteria related operators

Special OperatorsDescription
Is Null or Is Not NullDetermines whether a value is Null or Not Null, where Null is empty.
Note that a space in a field is not empty despite not being visible.
Like “pattern”Matches string/text values by using the wildcard operators such as ? for an unknown single character or * for any number of unknown characters.

This article uses Like “*” in the IIF statement to return all records where no Job No is specified.

Between # And #Determines whether a numeric or date value is found within a range.
In(val1,val2…)Determines whether a value is found within a set of values.

 

 

Screenshots

The screenshots were taken in Microsoft Access 2016. If you are using a different version of Access – for example, Access 2020 – your screen dialogue boxes may look slightly different.

Sours: https://www.acuitytraining.co.uk/news-tips/microsoft-access-using-if-queries/

2010 criteria in access

Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria you enter, it appears in the query results.

To add criteria to an Access query, open the query in Design view and identify the fields (columns) you want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the design grid and then enter the criterion in the Criteria row for that field. If you’re not sure how to make this happen, see Introduction to queries.

A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value. For example, = "Chicago" is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is "Chicago", Access includes the record in the query results.

Here are some examples of commonly used criteria you can use as a starting point to create your criteria. The examples are grouped by data types.

In this topic

Introduction to query criteria

A criterion is similar to a formula — it is a string that may consist of field references, operators, and constants. Query criteria are also referred to as expressions in Access.

The following tables shows some sample criteria and explains how they work.

Criteria

Description

>25 and <50

This criterion applies to a Number field, such as Price or UnitsInStock. It includes only those records where the Price or UnitsInStock field contains a value greater than 25 and less than 50.

DateDiff ("yyyy", [BirthDate], Date()) > 30

This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person's birthdate and today's date is greater than 30 are included in the query result.

Is Null

This criterion can be applied to any type of field to show records where the field value is null.

As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals.

Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the query in Design view. You then identify the fields for which you want to specify criteria. If the field is not already in the design grid, you add it by either dragging it from the query design window to the field grid, or by double-clicking the field (Double-clicking the field automatically adds it to the next empty column in the field grid.). Finally, you type the criteria in the Criteria row

Criteria that you specify for different fields in the Criteria row are combined by using the AND operator. In other words, the criteria specified in the City and BirthDate fields are interpreted like this:

City = "Chicago" AND BirthDate < DateAdd("yyyy", -40, Date())

Criteria for City and BirthDate

1. The City and BirthDate fields include criteria.

2. Only records where the value of the City field is Chicago will satisfy this criterion.

3. Only records of those who are at least 40 years old will satisfy this criterion.

4. Only records that meet both criteria will be included in the result.

What if you want only one of these conditions to be met? In other words, if you have alternate criteria, how do you enter them?

If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set, you use both the Criteria and the or rows in the design grid.

Alternate criteria

1. The City criterion is specified in the Criteria row.

2. The BirthDate criterion is specified in the or row.

Criteria specified in the Criteria and or rows are combined using the OR operator, as shown below:

City = "Chicago" OR BirthDate < DateAdd("yyyy", -40, Date())

If you need to specify more alternatives, use the rows below the or row.

Before you continue with the examples, note the following:

  • If the criteria is temporary or changes often, you can filter the query result instead of frequently modifying the query criteria. A filter is a temporary criterion that changes the query result without altering the design of the query. For more information about filters, see the article Apply a filter to view select records in an Access database.

  • If the criteria fields don't change, but the values you are interested in do change frequently, you can create a parameter query. A parameter query prompts the user for field values, and then uses those values to create the query criteria. For more information about parameter queries, see the article Use parameters in queries and reports.

Criteria for Text, Memo, and Hyperlink fields

Note: Beginning in Access 2013, Text fields are now named Short Text and Memo fields are now named Long Text.

The following examples are for the CountryRegion field in a query that is based on a table that stores contacts information. The criterion is specified in the Criteria row of the field in the design grid.

CountryRegion criteria

A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value, use the HyperlinkPart expression. The syntax for this expression is as follows: HyperlinkPart([Table1].[Field1],1) = "http://www.microsoft.com/", where Table1 is the name of the table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the URL you want to match.

To include records that...

Use this criterion

Query result

Exactly match a value, such as China

"China"

Returns records where the CountryRegion field is set to China.

Do not match a value, such as Mexico

Not "Mexico"

Returns records where the CountryRegion field is set to a country/region other than Mexico.

Begin with the specified string, such as U

Like U*

Returns records for all countries/regions whose names start with "U", such as UK, USA, and so on.

Note: When used in an expression, the asterisk (*) represents any string of characters — it is also called a wildcard character. For a list of such characters, see the article Access wildcard character reference.

Do not begin with the specified string, such as U

Not Like U*

Returns records for all countries/regions whose names start with a character other than "U".

Contain the specified string, such as Korea

Like "*Korea*"

Returns records for all countries/regions that contain the string "Korea".

Do not contain the specified string, such as Korea

Not Like "*Korea*"

Returns records for all countries/regions that do not contain the string "Korea".

End with the specified string, such as "ina"

Like "*ina"

Returns records for all countries/regions whose names end in "ina", such as China and Argentina.

Do not end with the specified string, such as "ina"

Not Like "*ina"

Returns records for all countries/regions that do not end in "ina", such as China and Argentina.

Contain null (or missing) values

Is Null

Returns records where there is no value in the field.

Do not contain null values

Is Not Null

Returns records where the value is not missing in the field.

Contain zero-length strings

"" (a pair of quotes)

Returns records where the field is set to a blank (but not null) value. For example, records of sales made to another department might contain a blank value in the CountryRegion field.

Do not contain zero-length strings

Not ""

Returns records where the CountryRegion field has a nonblank value.

Contains null values or zero-length strings

"" Or Is Null

Returns records where there is either no value in the field, or the field is set to a blank value.

Is not empty or blank

Is Not Null And Not ""

Returns records where the CountryRegion field has a nonblank, non-null value.

Follow a value, such as Mexico, when sorted in alphabetical order

>= "Mexico"

Returns records of all countries/regions, beginning with Mexico and continuing through the end of the alphabet.

Fall within a specific range, such as A through D

Like "[A-D]*"

Returns records for countries/regions whose names start with the letters "A" through "D".

Match one of two values, such as USA or UK

"USA" Or "UK"

Returns records for USA and UK.

Contain one of the values in a list of values

In("France", "China", "Germany", "Japan")

Returns records for all countries/regions specified in the list.

Contain certain characters at a specific position in the field value

Right([CountryRegion], 1) = "y"

Returns records for all countries/regions where the last letter is "y".

Satisfy length requirements

Len([CountryRegion]) > 10

Returns records for countries/regions whose name is more than 10 characters long.

Match a specific pattern

Like "Chi??"

Returns records for countries/regions, such as China and Chile, whose names are five characters long and the first three characters are "Chi".

Note: The characters ? and _, when used in an expression, represent a single character — these are also called wildcard characters. The character _ cannot be used in the same expression with the ? character, nor can it be used in an expression with the * wildcard character. You may use the wildcard character _ in an expression that also contains the % wildcard character.

Criteria for Number, Currency, and AutoNumber fields

The following examples are for the UnitPrice field in a query that is based on a table that stores products information. The criterion is specified in the Criteria row of the field in the query design grid.

UnitPrice criteria

To include records that...

Use this criterion

Query Result

Exactly match a value, such as 100

100

Returns records where the unit price of the product is $100.

Do not match a value, such as 1000

Not 1000

Returns records where the unit price of the product is not $1000.

Contain a value smaller than a value, such as 100

< 100
<= 100

Returns records where the unit price is less than $100 (<100). The second expression (<=100) displays records where the unit price is less than or equal to $100.

Contain a value larger than a value, such as 99.99

>99.99
>=99.99

Returns records where the unit price is greater than $99.99 (>99.99). The second expression displays records where the unit price is greater than or equal to $99.99.

Contain one of the two values, such as 20 or 25

20 or 25

Returns records where the unit price is either $20 or $25.

Contain a value that falls with a range of values

>49.99 and <99.99
-or-
Between 50 and 100

Returns records where the unit price is between (but not including) $49.99 and $99.99.

Contain a value that falls outside a range

<50 or >100

Returns records where the unit price is not between $50 and $100.

Contain one of many specific values

In(20, 25, 30)

Returns records where the unit price is either $20, $25, or $30.

Contain a value that ends with the specified digits

Like "*4.99"

Returns records where the unit price ends with "4.99", such as $4.99, $14.99, $24.99, and so on.

Note: The characters * and %, when used in an expression, represent any number of characters — these are also called wildcard characters. The character % cannot be used in the same expression with the * character, nor can it be used in an expression with the ? wildcard character. You may use the wildcard character % in an expression that also contains the _ wildcard character.

Contain null null (or missing) values

Is Null

Returns records where no value is entered in the UnitPrice field.

Contain non-null values

Is Not Null

Returns records where the value is not missing in the UnitPrice field.

Criteria for Date/Time fields

The following examples are for the OrderDate field in a query based on a table that stores Orders information. The criterion is specified in the Criteria row of the field in the query design grid.

OrderDate criteria

To include records that ...

Use this criterion

Query result

Exactly match a value, such as 2/2/2006

#2/2/2006#

Returns records of transactions that took place on Feb 2, 2006. Remember to surround date values with the # character so that Access can distinguish between date values and text strings.

Do not match a value, such as 2/2/2006

Not #2/2/2006#

Returns records of transactions that took place on a day other than Feb 2, 2006.

Contain values that fall before a certain date, such as 2/2/2006

< #2/2/2006#

Returns records of transactions that took place before Feb 2, 2006.

To view transactions that took place on or before this date, use the <= operator instead of the < operator.

Contain values that fall after a certain date, such as 2/2/2006

> #2/2/2006#

Returns records of transactions that took place after Feb 2, 2006.

To view transactions that took place on or after this date, use the >= operator instead of the > operator.

Contain values that fall within a date range

>#2/2/2006# and <#2/4/2006#

Returns records where the transactions took place between Feb 2, 2006 and Feb 4, 2006.

You can also use the Between operator to filter for a range of values, including the end points. For example, Between #2/2/2006# and #2/4/2006# is the same as >=#2/2/2006# and <=#2/4/2006# .

Contain values that fall outside a range

<#2/2/2006# or >#2/4/2006#

Returns records where the transactions took place before Feb 2, 2006 or after Feb 4, 2006.

Contain one of two values, such as 2/2/2006 or 2/3/2006

#2/2/2006# or #2/3/2006#

Returns records of transactions that took place on either Feb 2, 2006 or Feb 3, 2006.

Contain one of many values

In (#2/1/2006#, #3/1/2006#, #4/1/2006#)

Returns records where the transactions took place on Feb 1, 2006, March 1, 2006, or April 1, 2006.

Contain a date that falls in a specific month (irrespective of year), such as December

DatePart("m", [SalesDate]) = 12

Returns records where the transactions took place in December of any year.

Contain a date that falls in a specific quarter (irrespective of year), such as the first quarter

DatePart("q", [SalesDate]) = 1

Returns records where the transactions took place in the first quarter of any year.

Contain today's date

Date()

Returns records of transactions that took place on the current day. If today's date is 2/2/2006, you see records where the OrderDate field is set to Feb 2, 2006.

Contain yesterday's date

Date()-1

Returns records of transactions that took place the day before the current day. If today's date is 2/2/2006, you see records for Feb 1, 2006.

Contain tomorrow's date

Date() + 1

Returns records of transactions that took place the day after the current day. If today's date is 2/2/2006, you see records for Feb 3, 2006.

Contain dates that fall during the current week

DatePart("ww", [SalesDate]) = DatePart("ww", Date()) and Year( [SalesDate]) = Year(Date())

Returns records of transactions that took place during the current week. A week starts on Sunday and ends on Saturday.

Contain dates that fell during the previous week

Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())* 53 + DatePart("ww", Date()) - 1

Returns records of transactions that took place during the last week. A week starts on Sunday and ends on Saturday.

Contain dates that fall during the following week

Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1

Returns records of transactions that will take place next week. A week starts on Sunday and ends on Saturday.

Contain a date that fell during the last 7 days

Between Date() and Date()-6

Returns records of transactions that took place during the last 7 days. If today's date is 2/2/2006, you see records for the period Jan 24, 2006 through Feb 2, 2006.

Contain a date that belongs to the current month

Year([SalesDate]) = Year(Now()) And Month([SalesDate]) = Month(Now())

Returns records for the current month. If today's date is 2/2/2006, you see records for Feb 2006.

Contain a date that belongs to the previous month

Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) - 1

Returns records for the previous month. If today's date is 2/2/2006, you see records for Jan 2006.

Contain a date that belongs to the next month

Year([SalesDate])* 12 + DatePart("m", [SalesDate]) = Year(Date())* 12 + DatePart("m", Date()) + 1

Returns records for the next month. If today's date is 2/2/2006, you see records for Mar 2006.

Contain a date that fell during the last 30 or 31 days

Between Date( ) And DateAdd("M", -1, Date( ))

A month's worth of sales records. If today's date is 2/2/2006, you see records for the period Jan 2, 2006. to Feb 2, 2006

Contain a date that belongs to the current quarter

Year([SalesDate]) = Year(Now()) And DatePart("q", Date()) = DatePart("q", Now())

Returns records for the current quarter. If today's date is 2/2/2006, you see records for the first quarter of 2006.

Contain a date that belongs to the previous quarter

Year([SalesDate])*4+DatePart("q",[SalesDate]) = Year(Date())*4+DatePart("q",Date())- 1

Returns records for the previous quarter. If today's date is 2/2/2006, you see records for the last quarter of 2005.

Contain a date that belongs to the next quarter

Year([SalesDate])*4+DatePart("q",[SalesDate]) = Year(Date())*4+DatePart("q",Date())+1

Returns records for the next quarter. If today's date is 2/2/2006, you see records for the second quarter of 2006.

Contain a date that falls during the current year

Year([SalesDate]) = Year(Date())

Returns records for the current year. If today's date is 2/2/2006, you see records for the year 2006.

Contain a date that belongs to the previous year

Year([SalesDate]) = Year(Date()) - 1

Returns records of transactions that took place during the previous year. If today's date is 2/2/2006, you see records for the year 2005.

Contain a date that belongs to next year

Year([SalesDate]) = Year(Date()) + 1

Returns records of transactions with next year's date. If today's date is 2/2/2006, you see records for the year 2007.

Contain a date that falls between Jan 1 and today (year to date records)

Year([SalesDate]) = Year(Date()) and Month([SalesDate]) <= Month(Date()) and Day([SalesDate]) <= Day (Date())

Returns records of transactions with dates that fall between Jan 1 of the current year and today. If today's date is 2/2/2006, you see records for the period Jan 1, 2006 to to 2/2/2006.

Contain a date that occurred in the past

< Date()

Returns records of transactions that took place before today.

Contain a date that occurrs in the future

> Date()

Returns records of transactions that will take place after today.

Filter for null (or missing) values

Is Null

Returns records where the date of transaction is missing.

Filter for non-null values

Is Not Null

Returns records where the date of transaction is known.

Criteria for Yes/No fields

As an example, your Customers table has a Yes/No field named Active, used to indicate whether a customer's account is currently active. The following table shows how values entered in the Criteria row for a Yes/No field are evaluated.

Field value

Result

Yes, True, 1, or -1

Tested for a Yes value. A value of 1 or -1 is converted to "True" in the Criteria row after you enter it.

No, False, or 0

Tested for a No value. A value of 0 is converted to "False" in the Criteria row after you enter it.

No value (null)

Not tested

Any number other than 1, -1, or 0

No results if it's the only criteria value in the field

Any character string other than Yes, No, True, or False

Query fails to run due to Data type mismatch error

Criteria for other fields

Attachments    In the Criteria row, type Is Null to include records that do not contain any attachments. Type Is Not Null to include records that contain attachments.

Lookup fields    There are two types of Lookup fields: those that look up values in an existing data source (by using a foreign key), and those that are based on a list of values specified when the Lookup field is created.

Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are the same as for other text fields.

The criteria you can use in a Lookup field based on values from an existing datasource depend on the data type of the foreign key, rather than the data type of the data being looked up. For example, you may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data type. Because the field stores a number instead of text, you use criteria that work for numbers; that is, >2.

If you do not know the data type of the foreign key, you can inspect the source table in Design view to determine the data types of the field. To do this:

  1. Locate the source table in the Navigation Pane.

  2. Open the table in Design view by either:

    • Clicking the table, and then pressing CTRL+ENTER

    • Right-clicking the table, and then clicking Design View.

  3. The data type for each field is listed in the Data Type column of the table design grid.

Multivalued fields    Data in a multivalued field are stored as rows in a hidden table that Access creates and populates to represent the field. In query Design view, this is represented in the Field List by using an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the hidden table. To do this:

  1. Create a query containing the multivalued field, and open it in Design view.

  2. Expand the multivalued field by clicking the plus symbol (+) next to it — if the field is already expanded, this is a minus symbol (-). Just below the name of the field, you will see a field representing a single value of the multivalued field. This field will have the same name as the multivalued field, with the string .Value appended.

  3. Drag the multivalued field and its single value field to separate columns in the design grid. If you want to see only the complete multivalue field in your results, clear the Show check box for the single value field.

  4. Type your criteria in the Criteria row for the single value field, using criteria that is appropriate for whatever type of data the values represent.

  5. Each value in the multivalued field will be individually evaluated using the criteria you supply. For example, you may have a multivalued field that stores a list of numbers. If you supply the criteria >5 AND <3, any record where there is at least one value greater than 5 and one value less than 3 will match.

See Also

Introduction to queries

Create a simple select query

Sours: https://support.microsoft.com/en-us/office/examples-of-query-criteria-3197228c-8684-4552-ac03-aba746fb29d8
Microsoft Access 2010 Queries: Criteria Groups

Using dates, times, text, and values in criteria

MS-Access/ Getting Started

Access does its best to recognize the types of data you use in criteria; it relies on its best guess when providing characters to enclose the elements of the criteria expressions you come up with. You are, however, less likely to create criteria that Access doesn't understand if you use those characters yourself.

Table-2 lists the types of elements you may include in a criteria expression - as well as the character to use to make sure Access knows the element is text, a date, a time, a number, or a field name.

Dates, Time, and Text in CriteriaUse This Type of Data... In an Expression Like This... Text "text" Date #1-Feb-97# Time #12:00am# Number 10 Field name [field name]

You can refer to dates or times by using any allowed format. December 25, 2006, 12/25/06, and 25-Dec-06 are all formats that Access recognizes. You can use AM/PM or 24-hour time.

Using operators in criteria expressions

Don't be surprised if your criteria are frequently more complicated than "all records with California in the State field." You use operators in your criteria expressions to tell Access about more complex criteria.

Table-3 lists the operators that you're likely to use in an expression that specifies criteria.

Using Operators in Criteria
Relational OperatorWhat It Does
=Finds values equal to text, a number, or date/time ("equal to" is understood when you type a criterion without an operator - you don't need to type it).
<>Finds values not equal to text, a number, or date/time.
<Finds values less than a given value.
<=Finds values less than or equal to a given value.
>Finds values greater than a given value.
>=Finds values greater than or equal to a given value.
BETWEENFinds values between or equal to two values.
INFinds values or text included in a list.
LINEFinds matches to a pattern.

When you type your criterion, you don't have to tell Access the field name. Just put your criterion in the same column as the field, and Access applies the criterion to the field that appears in the same column.

Table-3 explains how different criteria affect the records that appear onscreen in the query datasheet.

Examples of Criteria with Operators
When Field1 Has This CriteriaThese Are the Records You See
<15Displays records where Field1 is less than 15.
<#9/1/03#Finds records where Field1 contains a date before September 1, 2003.
>15Finds records where Field1 is greater than 15.
>#12:00am#Finds records where Field1 is a time value after 12:00 a.m.
>[Max Price]Finds records where Field1 is more than the value in the field Max Price.
<>15Finds records where Field1 is not equal to 15.
>10 AND <20Finds records where Field1 is between 11 and 19.
>=10 AND <=20Finds records where Field1 is between 10 and 20, including 10 and 20.
BETWEEN 10 AND 20The same as >=10 AND <=20.
IN ("Virginia", "VA")Finds records where Field1 contains either Virginia or VA.
LIKE "A*"Finds records where Field1 begins with the letter A. You can use LIKE with wildcards such as * to tell Access in general terms what you're looking for. For more information on the wildcards that Access recognizes.

[Previous][Contents][Next]

Sours: https://sourcedaddy.com/ms-access/using-dates-times-text-and-values-criteria.html

You will also be interested:

You can use query criteria in Access to limit the results based on specific text values. For example the criterion, = "Chicago" shows all items that have the text Chicago. This article has several examples of query criteria that you can use with the Text data type that can help you get more specific query results and find the information that you want more quickly. If you’re not sure about using criteria see, applying criteria to a query.

Sample Text criteria and results

The table below shows how you can use some of the common criteria for Text data types. Try using the different criteria and see what results you get. If you happen to see no items in the results, you can recheck your criteria but it might just mean that there weren’t any items that exactly matched your criteria.

Note:  Access automatically adds the quote marks at the end of each criterion but you can add the quotes when you use text that might confuse the query. For example, if you use a phrase that has the words "and" or "or". Access interprets them as instructions.

For this result

Query Designer view

Criteria

To find all items that matches the text exactly.

Displays only contacts in the US.

query criteria to display specific word results

"Text"

The OR criteria row finds matches to multiple words or phrases.

Displays contacts in USA, China or Canada.

or criteria to match multiple words or phrases

"Text"

To exclude text, use the "Not" criteria followed by the word or phrase you want to exclude.

Displays contacts in all the cities except Boise.

To exclude a word or phrase, use the "Not" criteria followed by the word or phrase you want to exclude.

Not "Text"

To exclude text by multiple conditions

Displays all contacts that are not in Boise or New York or Las Vegas.

Tip:  Not Like "X*" finds all items except those starting with the specified letter.

To display all contacts not in UK or USA or France, use criteria Not In ("Text", "Text", "Text"…)

Not In ("Text", "Text", "Text"…)

You can use the NOT criterion with other expressions like,AND NOT followed by the text that you want to exclude from your search.

Displays all contacts from cities starting with the letter “L” except contacts in London.

Image of query design using NOT with AND NOT followed by the text to be excluded from search

Like “Text*” And Not "Text"

To search by last three letters of text.

Displays any contact whose country or region name end in "ina", like China and Argentina.

Query designer image showing criteria using folloeing operators, "like wildcard in a"

Like "*ina"

Displays last name of contacts where the country/region has no text.

Images shows ctriteria field in query designer with is null criteria

Is Null

Displays last names of contacts with a null value in the Country/Region column.

image of query designer with the is not criteria

Is Not Null

Displays last names of contacts whose city name is blank (but not null).

query designer with criteria set to show records with blank value field

"" (a pair of quotes)

Displays last names of contacts that have information in the city column.

query designer criteria set to country field not blank

Not ""

Displays contacts where the city information is neither blank nor null value.

query designer with criteria where City field is neither set to null nor is blank.

Is Not Null And Not ""

Tips: 

  • If you’d like to check out the query syntax in SQL (Structured Query Language), click the SQL View on the toolbar at the bottom right of your screen.

  • SQL view on the toolbar

Top of Page

Sours: https://support.microsoft.com/en-us/office/apply-criteria-to-text-values-202548b6-b065-4387-800b-2456a7099bca


38217 38218 38219 38220 38221