Microsoft access expression

Microsoft access expression DEFAULT

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

When you use Microsoft Access, you often need to work with values that are not directly in your data. For example, you want to calculate sales tax on an order, or calculate the total value of the order itself. You can calculate these values by using expressions. To use expressions, you write them by using proper syntax. Syntax is the set of rules by which the words and symbols in an expression are correctly combined. Initially, expressions in Access are a little bit hard to read. But with a good understanding of expression syntax and a little practice, it becomes much easier.

In this article

Overview of expressions

An expression is a combination of some or all of the following: built-in or user-defined functions, identifiers, operators, values, and constants that evaluate to a single value.

The components of an expression

For example, the following expression contains common components:

  • Sum() is a built-in function

  • [Purchase Price] is an identifier

  • * is a mathematical operator

  • 0.08 is a constant

This expression can be used in a text box in a form footer or report footer to calculate sales tax for a group of items.

Expressions can be much more complex or much simpler than this example. For example, this Boolean expression consists of just an operator and a constant:

This expression returns:

  • True when it is compared to a number that is greater than 0.

  • False when it is compared to a number that is less than 0.

You can use this expression in the Validation Rule property of a control or table field to ensure that only positive values are entered. Expressions are used in many places to perform calculations, manipulate characters, or test data. Tables, queries, forms, reports, and macros all have properties that accept an expression. For example, you can use expressions in the Control Source and Default Value properties for a control. You can also use expressions in the Validation Rule property for a table field.

Top of Page

Components of expressions

To build an expression, you combine identifiers by using functions, operators, constants, and values. Any valid expression must contain at least one function or at least one identifier, and can also contain constants or operators. You can also use an expression as part of another expression — typically as an argument of a function. This is called nesting an expression.

  • Identifiers    The general form is:

    You only have to specify enough parts of an identifier to make it unique in the context of your expression. It is not uncommon for an identifier to take the form [Object name].

  • Functions    The general form is:

    One of the arguments is usually an identifier or an expression. Some functions do not require arguments. Before you use a particular function, review that function's syntax. For more information, see Functions (arranged by category).

  • Operators    The general form is:

    There are exceptions to this form, as detailed in the Operators section.

  • Constants    The general form is:

  • Values    Values can occur in many locations in an expression.

Top of Page

Objects, collections, and properties

All of the tables, queries, forms, reports, and fields in an Access database are individually known as objects. Every object has a name. Some objects are already named, such as the Contacts table in a database created from the Microsoft Office Access Contacts template. When you create a new object, you give it a name.

The set of all members of a particular type of object is known as a collection. For example, the set of all tables in a database is a collection. Some objects that are a member of a collection in your database can also be collections that contain other objects. For example, a table object is a collection that contains field objects.

Objects have properties, which describe, and provide a way to change, the object's characteristics. For example, a query object has a Default View property that both describes and lets you specify how the query will appear when you run it.

The following diagram illustrates the relationship between collections, objects, and properties:

Relationships of collections to objects to properties

3 A property

2 An object

1 A collection

Top of Page

Identifiers

When you use an object, collection, or property in an expression, you refer to that element by using an identifier. An identifier includes the name of the element that you are identifying and also the name of the element to which it belongs. For instance, the identifier for a field includes the name of the field and the name of the table to which the field belongs. An example of such an identifier is:

In some cases, the name of an element works by itself as an identifier. This is true when the name of the element is unique in the context of the expression that you are creating. The rest of the identifier is implied by the context. For example, if you are designing a query that uses only one table, the field names alone will work as identifiers, because the field names in a table must be unique in that table. Because you are using only one table, the table name is implied in any identifier that you use in the query to refer to a field.

In other cases, you must be explicit about the parts of an identifier for a reference to work. This is true when an identifier is not unique in the context of the expression. When there is ambiguity, you must explicitly denote enough parts of the identifier to make it unique in context. For example, suppose you are designing a query that uses a table named Products and a table named Orders, and both tables have a field named ProductID. In such a case, the identifier that you use in the query to refer to either ProductID field must include the table name in addition to the field name. For example:

Identifier operators    There are three operators that you can use in an identifier:

  • The bang operator (!)

  • The dot operator (.)

  • The square brackets operator ([ ])

You use these operators by surrounding each part of the identifier with square brackets, and then joining them by using either a bang or a dot operator. For example, an identifier for a field named Last Name in a table named Employees can be expressed as [Employees]![Last Name]. The bang operator tells Access that what follows is an object that belongs to the collection that precedes the bang operator. In this case, [Last Name] is a field object that belongs to the collection [Employees], which is itself a table object.

Strictly speaking, you do not always have to type square brackets around an identifier or partial identifier. If there are no spaces or other special characters in the identifier, Access automatically adds the brackets when it reads the expression. However, it is a good practice to type the brackets yourself — this helps you to avoid errors, and also functions as a visual clue that a particular part of an expression is an identifier.

Top of Page

Functions, operators, constants, and values

To create an expression, you need more than identifiers — you need to perform an action of some sort. You use functions, operators, and constants to perform actions in an expression.

Functions

A function is a procedure that you can use in an expression. Some functions, such as Date, do not require any input to work. Most functions, however, do require input, called arguments.

In the example at the beginning of this article, the DatePart function uses two arguments: an interval argument, with a value of "yyyy" and a date argument, with a value of [Customers]![BirthDate]. The DatePart function requires at least these two arguments (interval and date), but can accept up to four arguments.

The following list shows some functions that are commonly used in expressions. Click the link for each function for more information about the syntax that you use with that function.

  • The Date function is used to insert the current system date into an expression. It is commonly used with the Format function, and is also used with field identifiers for fields that contain date/time data.

  • The DatePart function is used to determine or extract part of a date — usually a date that is obtained from a field identifier, but sometimes a date value that is returned by another function, such as Date.

  • The DateDiff function is used to determine the difference between two dates — usually between a date that is obtained from a field identifier and a date that is obtained by using the Date function.

  • The Format function is used to apply a format to an identifier and the results of another function.

  • The IIf function is used to evaluate an expression as true or false, and then return one value if the expression evaluates as true, and a different value if the expression evaluates as false.

  • The InStr function is used to search for the position of a character or string within another string. The string that is searched is usually obtained from a field identifier.

  • The Left, Mid, and Right functions are used to extract characters from a string, starting with the leftmost character (Left), a specific position in the middle (Mid), or with the rightmost character (Right). They are commonly used with the InStr function. The string from which these functions extract characters is usually obtained from a field identifier.

For a list of functions, see Functions (arranged by category).

Operators

An operator is a word or symbol that indicates a specific arithmetic or logical relationship between the other elements of an expression. Operators can be:

  • Arithmetic, such as the plus sign (+).

  • Comparison, such as the equal sign (=).

  • Logical, such as Not.

  • Concatenation, such as &.

  • Special, such as Like.

Operators are generally used to indicate a relationship between two identifiers. The following tables describe the operators that you can use in Access expressions.

Arithmetic

You use the arithmetic operators to calculate a value from two or more numbers or to change the sign of a number from positive to negative.

Operator

Purpose

Example

+

Sum two numbers.

[Subtotal]+[SalesTax]

-

Find the difference between two numbers or indicate the negative value of a number.

[Price]-[Discount]

*

Multiply two numbers.

[Quantity]*[Price]

/

Divide the first number by the second number.

[Total]/[ItemCount]

\

Round both numbers to integers, divide the first number by the second number, and then truncate the result to an integer.

[Registered]\[Rooms]

Mod

Divide the first number by the second number and return only the remainder.

[Registered] Mod [Rooms]

^

Raise a number to the power of an exponent.

Number ^ Exponent

Comparison

You use the comparison operators to compare values and return a result that is either true, false, or Null (an unknown value).

Operator

Purpose

<

Determine if the first value is less than the second value.

<=

Determine if the first value is less than or equal to the second value.

>

Determine if the first value is greater than the second value.

>=

Determine if the first value is greater than or equal to the second value.

=

Determine if the first value is equal to the second value.

<>

Determine if the first value is not equal to the second value.

In all cases, if either the first value or the second value is Null, the result is then also Null. Because Null represents an unknown value, the result of any comparison with Null is also unknown.

Logical

You use the logical operators to combine two values and return either a true, false, or Null result. You might also see the logical operators referred to as Boolean operators.

Operator

Usage

Description

And

Expr1 And Expr2

True when Expr1 and Expr2 are true.

Or

Expr1 Or Expr2

True when either Expr1 or Expr2 is true.

Eqv

Expr1 Eqv Expr2

True when both Expr1 and Expr2 are true or both Expr1 and Expr2 are false.

Not

Not Expr

True when Expr is not true.

Xor

Expr1 Xor Expr2

True when either Expr1 is true, or Expr2 is true, but not both.

Concatenation

You use the concatenation operators to combine two text values into one string.

Operator

Usage

Description

&

string1 & string2

Combines two strings to form one string.

+

string1 + string2

Combines two strings to form one string and propagates null values.

Special

You use the special operators as described in the following table.

Operator

Description

For More Information

Is Null or Is Not Null

Determines whether a value is Null or Not Null.

Like "pattern"

Matches string values by using wildcard operators ? and *.

Like Operator

Between val1 And val2

Determines whether a numeric or date value falls within a range.

Between...And Operator

In(string1,string2...)

Determines whether a string value is contained within a set of string values.

In Operator

Constants

A constant is a known value that does not change and that you can use in an expression. There are four commonly used constants in Access:

  • True indicates something that is logically true.

  • False indicates something that is logically false.

  • Null indicates the lack of a known value.

  • "" (empty string) indicates a value that is known to be empty.

Constants can be used as arguments to a function, and can be used in an expression as part of a criterion. For example, you can use the empty string constant ("") as part of a criterion for a column in a query to evaluate the field values for that column, by entering the following as the criterion: <>"". In this example, <> is an operator and "" is a constant. Used together, they indicate that the identifier to which they are applied should be compared to an empty string. The expression that results is true when the identifier's value is anything other than an empty string.

Be careful when using the Null constant. In most cases, using Null in conjunction with a comparison operator will result in an error. If you want to compare a value to Null in an expression, use the Is Null or the Is Not Null operator.

Values

You can use literal values in your expressions, such as the number 1,254 or the string "Enter a number between 1 and 10." You can also use numeric values, which can be a series of digits, including a sign and a decimal point, if needed. In the absence of a sign, Access assumes a positive value. To make a value negative, include the minus sign (-). You can also use scientific notation. To do so, include "E" or "e" and the sign of the exponent (for example, 1.0E-6).

When you use text strings, place them within quotation marks to help make sure that Access interprets them correctly. In some circumstances, Access supplies the quotation marks for you. For example, when you type text in an expression for a validation rule or for query criteria, Access surrounds your text strings with quotation marks automatically.

For example, if you type the text Paris, Access displays "Paris" in the expression. If you want an expression to produce a string that is actually enclosed in quotation marks, you enclose the nested string either in single (') quotation marks or within three sets of double (") quotation marks. For example, the following expressions are equivalent:

To use date/time values, enclose the values in pound signs (#). For example, #3-7-17#, #7-Mar-17#, and #Mar-7-2017# are all valid date/time values. When Access encounters a valid date/time value that is enclosed in # characters, it automatically treats the value as a Date/Time data type.

Top of Page

The nesting limit for expressions in a web database is 65

Expressions nested more than 65 levels deep won’t work in the browser, so you shouldn’t use any such expressions in an Access web database. You won’t get any error messages – the expression just won’t work.

The use of the &, AND, and OR operators can create additional nesting levels on the server that aren't reflected in the Access client. For example the expression "a" & "b" & "c" is not nested in the Expression Builder, but in SharePoint it becomes concatenate.Db("a", concatenate.Db("b", "c") ). This translation creates one level of nesting. Using many consecutive &, AND, or OR operators in a single expression can cause you to exceed the server's nesting limit of 65, at which point the expression won’t work in the browser.

Top of Page

Use the Expression Builder

Introduction to expressions

Build an expression

Examples of expressions

Sours: https://support.microsoft.com/en-us/office/guide-to-expression-syntax-ebc770bc-8486-4adc-a9ec-7427cce39a90
  1. Diy pc case
  2. Handmade get well cards ideas
  3. New features of google chrome
  4. How to tune bmw

Returns one of two parts, depending on the evaluation of an expression.

You can use IIf anywhere you can use expressions. You use IIf to determine if another expression is true or false. If the expression is true, IIf returns one value; if it is false, IIf returns another. You specify the values IIf returns.

See some examples

Syntax

IIf(expr, truepart, falsepart)

The IIf function syntax has these arguments:

Argument

Description

expr

Required. Expression you want to evaluate.

truepart

Required. Value or expression returned if expr is True.

falsepart

Required. Value or expression returned if expr is False.

Remarks

IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True.

Examples

Use IIf on a form or report    Suppose you have a Customers table that contains a field named CountryRegion. In a form, you want to denote whether Italian is the first language of the contact. You can add a control and use IIf in its Control Source property, like so:

=IIf([CountryRegion]="Italy", "Italian", "Some other language")

When you open the form in Form view, the control displays "Italian" whenever the value for CountryRegion is Italy, and "Some other language" whenever CountryRegion is any other value.

Use IIf in complex expressions    You can use any expression as any part of an IIf statement. You can also "nest" IIf expressions, allowing you to evaluate a series of dependent expressions. To continue with the preceding example, you might want to test for several different CountryRegion values, and then display the appropriate language depending on which value exists:

=IIf([CountryRegion]="Italy", "Italian", IIf([CountryRegion]="France", "French", IIf([CountryRegion]="Germany", "German", "Some other language")))

The text "Some other language" is the falsepart argument of the innermost IIf function. Since each nested IIf function is the falsepart argument of the IIf function that contains it, the text "Some other language" is only returned if all the expr arguments of all the IIf functions evaluate to False.

For another example, suppose you work at a library. The library database has a table named Check Outs that contains a field, named Due Date, that contains the date a particular book is due back. You can create a form that indicates the status of a checked out item in a control by using the IIf function in that control’s Control Source property, like so:

=IIf([Due Date]<Date(),"OVERDUE",IIf([Due Date]=Date(),"Due today","Not Yet Due"))

When you open the form in Form view, the control displays "OVERDUE" if the value of Due Date is less than the current date, "Due today" if it is equal to the current date, and "Not Yet Due" otherwise.

Note: To use logical operators such as "And" or "Or" in the expr argument of the IIf function, you must enclose the logical expression in the Eval function. See the example table that follows.

Use IIf in a query

The IIf function is frequently used to create calculated fields in queries. The syntax is the same, with the exception that in a query, you must preface the expression with a field alias and a colon (:) instead of an equal sign (=). To use the preceding example, you would type the following in the Field row of the query design grid:

Language: IIf([CountryRegion]="Italy", "Italian", "Some other language")

In this case, "Language:" is the field alias.

For more information about creating queries and calculated fields, see the article Create a simple select query.

Use IIf in VBA code

Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.

This example uses the IIf function to evaluate the TestMe parameter of the CheckIt procedure and returns the word "Large" if the amount is greater than 1000; otherwise, it returns the word "Small".

More examples

Expression

Results

=IIf([AirportCode]="ORD","Chicago",IIf([AirportCode]="ATL","Atlanta",IIf([AirportCode]="SEA","Seattle","Other")))

If [AirportCode] is "ORD", return "Chicago". Otherwise, if [AirportCode] is "ATL", return "Atlanta". Otherwise, if [AirportCode] is "SEA", return "Seattle". Otherwise, return "Other".

=IIf([ShipDate]<Date(),"Shipped",IIf([ShipDate]=Date(),"Shipping today","Unshipped"))

If [ShipDate] is prior to today's date, return "Shipped". Otherwise, if [ShipDate] equals today's date, return "Shipping today". Otherwise, return "Unshipped."

=IIf([PurchaseDate]<#1/1/2008#,"Old","New")

If [PurchaseDate] is prior to 1/1/2008, return "Old". Otherwise, return "New."

=IIf(Eval([Volts] Between 12 And 15 And [Amps] Between 0.25 And 0.3),"OK","Out of calibration")

If [Volts] is between 12 and 15 and [Amps] is between 0.25 and 0.3, return "OK". Otherwise, return "Out of calibration."

=IIf(Eval([CountryRegion] In ("Canada","USA","Mexico")),"North America","Other")

If [CountryRegion] is "Canada", "USA", or "Mexico", return "North America". Otherwise, return "Other".

=IIf([Average]>=90,"A",IIf([Average]>=80,"B",IIf([Average]>=70,"C",IIf([Average]>=60,"D","F"))))

If [Average] is 90 or greater, return "A". Otherwise, if [Average] is 80 or greater, return "B". Otherwise, if [Average] is 70 or greater, return "C". Otherwise, if [Average] is 60 or greater, return "D". Otherwise, return "F".

Note: If you are using the IIf function to create a calculated field in a query, replace the equal sign (=) with a field alias and a colon (:). For example, Status: IIf([ShipDate]<Date(),"Shipped",IIf([ShipDate]=Date(),"Shipping today","Unshipped"))

Sours: https://support.microsoft.com/en-us/office/iif-function-32436ecf-c629-48a3-9900-647539c764e3
Microsoft Access Conditional Formatting Expression Is

You can use expressions to do all kinds of things with your data. For example, determine how many days have passed since an order was shipped, or combine a FirstName and a LastName into a FullName. The following sections show you how to create an expression step-by-step.

In this article

Calculate values for controls in forms and reports

When you use an expression as the data source for a control, you create a calculated control. For example, suppose that you have a report that displays multiple inventory records, and you want to create a total in the report footer that sums all the line items on the report.

A total on a report

To calculate the total, place a text box control in the report footer, and then set the ControlSource property of the text box to the following expression:

In this case, table_field is the name of the field that contains your subtotal values. That field can come from a table or a query. The Sum function calculates the total for all the values of table_field.

PROCEDURE

  1. In the Navigation Pane, right-click the form that you want to change, and then click Layout View or Design View on the shortcut menu.

  2. Select the control into which you want to enter an expression.

  3. If the Property Sheet is not already displayed, press F4 to display it.

  4. To manually create an expression, on the Data tab in the property sheet, click the ControlSource property of the text box, and then type = followed by the rest of your expression. For example, to calculate the subtotal shown above, type =Sum([table_field]), making sure that you substitute the name of your field for table_field.

  5. To create an expression by using the Expression Builder, click the Build button Button image in the property box.

    After completing your expression, the property sheet will look like this:

    An expression in the Control Source property of a text box

Top of Page

Use expressions as query criteria

You use criteria in a query to narrow down query results. You enter the criteria as an expression, and Access returns only those rows that match the expression.

For example, suppose that you want to see all the orders whose shipped date occurred in the first three months of the year 2017. To enter the criteria, you type the following expression in the Criteria cell for the Date/Time column in your query. This example uses a Date/Time column called ShippedDate. To define a date range, enter your criteria in this manner:

The ShippedDate column will resemble the following.

An expression in the Criteria row of the query grid

For each record in the Orders table, if the value in the ShippedDate column falls in the date range that you specify, the record is included in the query output. Note that in the expression, you enclose the dates with pound signs (#). Access treats a value enclosed in pound signs as a Date/Time data type. Treating those values as date/time data enables you to perform calculations on those values, such as subtracting one date from another.

PROCEDURE

  1. In the Navigation Pane, right-click the query that you want to change, and then click Design View on the shortcut menu.

  2. Click in the Criteria cell in the column for which you want to enter your criteria.

  3. To manually create your expression, type your criteria expression. Do not precede the criteria expression with the = operator.

  4. To create your expression by using the Expression Builder, on the ribbon, click Design, and then in the Query Setup group, click BuilderButton image.

    If you want a larger area in which to edit the expression, place the cursor in the Criteria cell and then press SHIFT+F2 to display the Zoom box:

    An expression in the Zoom dialog box.

    Tip   To make the text more readable, select Font.

Top of Page

Create a calculated field in a query

Suppose you’re designing a query, and you want to display the results of a calculation using other fields in the query. To create the calculated field, you enter an expression in a blank cell in the Field row in your query. For example, if you have a query that contains a Quantity field and a Unit Price field, you can multiply the two to create a calculated field for Extended Price by entering the following expression in the Field row of the query:

Prefacing the expression with the text Extended Price: names the new column Extended Price. This name is often called an alias. If you do not supply an alias, Access will create one, such as Expr1.

Using an expression to create a calculated field in a query

When you run the query, Access performs the calculation on each row, as shown in the following illustration:

A calculated field, shown in Datasheet view

PROCEDURE

  1. In the Navigation Pane, right-click the query that you want to change, and then click Design View on the shortcut menu.

  2. Click the Field cell in the column where you want to create the calculated field.

  3. To manually create your expression, type your expression.

    Do not precede the criteria expression with the = operator; instead, begin the expression with a descriptive label followed by a colon. For example, type Extended Price: to provide the label for an expression that creates a calculated field called Extended Price. Then, enter the criteria for your expression after the colon.

  4. To create your expression by using the Expression Builder, on the ribbon, click Design, and then in the Query Setup group, click Builder.

Top of Page

Create a calculated field in a table

In Access, you can create a calculated field in a table. This eliminates the need for a separate query to do calculations. For example, if you have a table that lists the quantity, price and tax rate for each item in an order, you could add a calculated field that shows the total price, like so:

The calculation cannot include fields from other tables or queries and the results of the calculation are read-only.

PROCEDURE

  1. Open the table by double-clicking it in the Navigation Pane.

  2. Scroll horizontally to the rightmost column in the table, and click the Click to Add column heading.

  3. In the list that appears, click Calculated Field, and then click the data type that you want for the result. Access displays the Expression Builder.

  4. Enter the calculation that you want for this field, for example:

    For a calculated field, you don’t start the expression with an equal sign (=).

  5. Click OK.

    Access adds the calculated field, and then highlights the field header so that you can type a field name.

  6. Type a name for the calculated field, and then press ENTER.

Top of Page

Set default values for a table field

You can use an expression to specify a default value for a field in a table – a value that Access uses for new records unless another value is provided. For example, suppose that you want to automatically insert the date and time into a field called OrderDate whenever someone adds a new record. To do this, you might use the following expression:

PROCEDURE

  1. In the Navigation Pane, double-click the table that you want to change.

    Access opens the table in Datasheet view.

  2. Select the field that you want to change.

  3. On the ribbon, click Fields, and in the Properties group, click Default Value. Access displays the Expression Builder.

  4. Type your expression in the box, making sure to begin the expression with an equal sign (=).

Note    If you bind a control to a table field and both the control and table field have default values, the control's default value takes precedence over the table field.

Top of Page

Set default values for controls

Another common place to use an expression is in the Default Value property of a control. The Default Value property of a control is like the Default Value property of a field in a table. For example, to use the current date as the default value for a text box, you can use the following expression:

This expression uses the Date function to return the current date, but not the time. If you bind the text box to a table field, and the field has a default value, the control's default value takes precedence over the table field. It often makes better sense to set the Default Value property for the field in the table. That way, if you base several controls for different forms on the same table field, the same default value will apply to each control, helping ensure consistent data entry on each form.

PROCEDURE

  1. In the Navigation Pane, right-click the form or report that you want to change, and then click Design View or Layout View on the shortcut menu.

  2. Select the control that you want to change.

  3. If the property sheet is not already displayed, press F4 to display it.

  4. Click the All tab on the property sheet, and then click the Default Value property box.

  5. Type the expression, or click the Build button Button image in the property box to create an expression by using the Expression Builder.

Top of Page

Add a validation rule to a table field or record

Expressions are very useful for validating data as it is entered into the database – keeping bad data out. In tables, there are two kinds of validation rules: Field validation rules (which prevent users from entering bad data in a single field), and record validation rules (which prevent users from creating records that don't meet the validation rule). You use expressions for both kinds of validation rules.

For example, suppose that you have a table named Inventory with a field that is named Units On Hand, and you want to set a rule that forces users to enter a value greater than or equal to zero. In other words, inventory can never be a negative number. You can do this by using the following expression as a field validation rule in the Units On Hand field:

PROCEDURE: Enter a field or record validation rule

  1. In the Navigation Pane, double-click the table that you want to change. Access opens the table in Datasheet view.

  2. For a field validation rule, select the field that you want to change.

  3. On the ribbon, click Fields, then in the Field Validation group click Validation, and then click Field Validation Rule or Record Validation Rule. Access displays the Expression Builder.

  4. Begin typing the criteria that you want. For example, for a field validation rule that requires that all values are greater than or equal to zero, type the following:

    Do not precede the expression with an equal sign (=).

Validation rule expressions are Boolean, which means that they are either True or False for any given input value. A validation rule must be True for the value or Access doesn’t save the input and displays a validation message indicating the error. In this example, if you enter a value for the Units On Hand field that is less than zero, the validation rule is False, and Access does not accept the value. If you haven't entered a validation message as described in the following section, Access displays its own message to indicate that the value you entered is prohibited by the validation rule for the field.

PROCEDURE: Enter a validation message

To make your database easier to use, you can enter custom validation messages. These replace the generic messages that Access displays when data does not match a validation rule. You can use custom validation message to provide specific information that helps the user enter the correct data, for example, "The Units On Hand value cannot be a negative number."

  1. In the Navigation Pane, double-click the table that you want to change.

    Access opens the table in Datasheet view.

  2. For a field validation message, select the field to which you added the validation rule.

  3. On the ribbon, click Fields, then in the Field Validation group click Validation, and then click Field Validation Message or Record Validation Message.

  4. In the Enter Validation Message dialog box, type the message that you want to appear when data does not match the validation rule, and then click OK.

Top of Page

Add a validation rule to a control

In addition to table fields and records, controls also have a Validation Rule property that can accept an expression. For example, suppose that you use a form to enter the date range for a report, and you want to ensure that the beginning date isn't earlier than 1/1/2017. You can set the Validation Rule and ValidationText properties for the text box where you enter the beginning date to the following:

Property

Setting

Validation Rule

>=#1/1/2017#

Validation Text

You cannot enter a date earlier than 1/1/2017.

If you try to enter a date earlier than 1/1/2017, a message appears, and displays the text in the ValidationText property. If no text was entered in the ValidationText property box, Access displays a generic message. After you click OK, Access returns you to the text box.

Setting a validation rule for a table field enforces the rule throughout the database, wherever that field is modified. Alternatively, setting a validation rule for a control on a form enforces the rule only when that form is being used. Setting validation rules separately for table fields and for controls on forms can be useful if you want to establish different validation rules for different users.

PROCEDURE

  1. In the Navigation Pane, right-click the form or report that you want to change, and then click Design View or Layout View on the shortcut menu.

  2. Right-click the control that you want to change, and then click Properties on the shortcut menu. Access displays the property sheet for the control.

  3. Click the All tab, and then click the Validation Rule property box.

  4. Type the expression, or click the Build button Button image in the property box to create an expression by using the Expression Builder.

    Do not precede the expression with the = operator.

  5. To customize the text that appears if a user enters data that does not match the validation rule, type the text that you want in the Validation Text property.

Top of Page

Group and sort data in reports

You use the Group, Sort, and Total pane to define grouping levels and sorting orders for the data in a report. Most typically, you group or sort on a field that you select from a list. However, if you want to group or sort on a calculated value, you can enter an expression instead.

Grouping is the process of combining columns that contain duplicate values. For example, suppose that your database contains sales information for offices in different cities, and that one of the reports in your database is named "Sales by City." The query that provides the data for that report groups the data by your city values. This type of grouping can make your information easier to read and understand.

In contrast, sorting is the process of imposing a sort order on the rows (the records) in your query results. For example, you can sort records by their primary key values (or another set of values in another field) in either ascending or descending order, or you can sort the records by one or more characters in a specified order, such as alphabetical order.

PROCEDURE: Add grouping and sorting to a report

  1. In the Navigation Pane, right-click the report that you want to change, and then click Layout View or Design View on the shortcut menu.

  2. On the ribbon, click Design, and in the Grouping & Totals group, click Group & Sort. The Group, Sort, and Total pane appears below the report.

  3. To add a grouping level to the report, click Add a group.

  4. To add a sort order to the report, click Add a sort.

    A new group level or sort order appears in the pane, plus a list of the fields that provide data for the report. This figure shows a typical new group level (grouping on Category) and sort order (sorting on Manufacturer), plus a list that contains the available fields for grouping and sorting:

    Choosing the expression option in the Group, Sort, and Total pane

  5. Under the list of available fields, click expression to start using the Expression Builder.

  6. Enter the expression you want to use in the expression box (the upper box) of the Expression Builder. Make sure that you begin the expression with the equal (=) operator.

PROCEDURE: Add an expression to an existing group or sort

  1. In the Navigation Pane, right-click the report that you want to change, and then click Layout View or Design View on the shortcut menu.

  2. Click the group level or sort order that you want to change.

  3. Click the down arrow next to Group on (for grouping levels) or Sort by (for sort orders). A list that contains the available fields appears.

  4. At the bottom of the list that contains the fields, click expression to start using the Expression Builder.

  5. Type your expression in the expression box (the upper box) of the Expression Builder. Make sure that you begin your expression with the equal (=) operator.

Top of Page

Control which macro actions are run

In some cases, you might want to carry out an action or series of actions in a macro only if a particular condition is true. For example, suppose that you want a macro action to run only when the value in a text box is greater than or equal to 10. To set this rule, you use an expression in an If block in the macro to define the condition in the macro.

In this example, assume the text box is named "Items." The expression that sets the condition is:

An expression used in an If block in a macro

PROCEDURE

  1. In the Navigation Pane, right-click the macro that you want to change, and then click Design View on the shortcut menu.

  2. Click the If block that you want to modify, or add an If block from the Action Catalog pane.

  3. Click the top line of the If block.

  4. Type your conditional expression in the box, or click the Build button Button image next to the expression box to start using the Expression Builder.

The expression you type must be Boolean, meaning it is either True or False. The macro actions within the If block run only when the condition is True.

Top of Page

See Also

Use the Expression Builder

Introduction to expressions

Guide to expression syntax

Examples of expressions

Sours: https://support.microsoft.com/en-us/office/learn-to-build-an-expression-20c385ee-accd-4306-bc7b-adf11f26948a

Expression microsoft access

You can use expressions for a wide variety of tasks in Microsoft Access, such as performing mathematical calculations, combining or extracting text, or validating data. This article provides overview information about expressions — when to use them, what their component parts are, and how they compare with Microsoft Excel formulas.

In this article

Overview of expressions

Think of it this way: when you want Access to do something, you have to speak its language. For example, suppose you want to tell Access "Look at the BirthDate field in the Customers table and tell me the year of the customer's birth." You can write this expression as:

This expression consists of the DatePart function and two argument values: "yyyy" and [Customers]![BirthDate].

Let's examine this expression in more detail.

Expression example

1 DatePart is a function that examines a date and returns a specific portion. In this case, the first two arguments are used.

2 The interval argument tells Access which part of the date to return — in this case, "yyyy" tells Access that you want only the year part of the date returned.

3 The date argument tells Access where to look for the date value — in this case, [Customers]![BirthDate] tells Access to look for the date in the BirthDate field of the Customers table.

Ways to use expressions

You can use expressions in the following ways:

  • Calculate values that do not exist directly in your data. You can calculate values in fields in tables and queries, and you can also calculate values in controls on forms and reports.

  • Define a default value for a table field or for a control on a form or report. These default values appear whenever you open a table, form, or report.

  • Create a validation rule to control what values users can enter in a field or control.

  • Define query criteria to limit results to a desired subset.

Calculate values

One of the most common ways to use expressions in Access is to calculate values that don't exist directly in your data. A column in a table or query that results from such a calculation is called a calculated field. You can create a calculated field that combines two or more table fields. For example, many tables store first and last names in separate fields. If you want to combine those first and last names and then display them in a single field, you can create a calculated field in the table or in a query:

In this case, the ampersands (&) combine the value in the FirstName field, a space character (a space enclosed in quotation marks), and the value in the LastName field.

Define a default value

You can also use expressions in Access to provide a default value for a field in a table or for a control. For example, to set the default value for a date field to the current date, in the Default Value property box for that field, you type:

Create a validation rule

In addition, you can use an expression to set a validation rule. For example, you can use a validation rule in a control or table field to require that the date that is entered is greater than or equal to the current date. In that case, you set the value in the Validation Rule property box to:

Define query criteria

Finally, you can use an expression to set criteria for a query. For example, suppose that you want to see product sales for the orders that were shipped within a certain time frame. You can enter criteria to define a date range, and Access returns only the rows that match the criteria. For example, your expression might look like this:

When you add criteria to the query and then run the query, it returns only those values that match the specified dates.

Examples of expressions

The following table lists some sample Access expressions and how they are typically used:

Expression

Purpose

=[RequiredDate]-[ShippedDate]

Calculates the difference between the date values in two text box controls (called RequiredDate and ShippedDate) on a report.

Date()

Sets the default value for a Date/Time table field to the current date.

Between #1/1/2017# And #12/31/2017#

Specifies criteria for a Date/Time field in a query.

=[Orders Subform].Form!OrderSubtotal

Returns the value of the OrderSubtotal control on the Orders subform that is on the Orders form.

>0

Sets a validation rule for a numeric field in a table — users must enter values greater than zero.

Some expressions begin with the equal (=) operator, and others do not. When you calculate a value for a control on a form or report, you use the = operator to start the expression. In other instances, such as when you type an expression in a query or in the DefaultValue or ValidationRule property of a field or control, you don't use the = operator unless you are adding the expression to a Text field in a table. In some cases, such as when you add expressions to queries, Access removes the = operator automatically.

Components of expressions

An expression consists of a number of possible components that you can use, alone or in combination, to produce a result. These components include:

  • Identifiers    The names of table fields or controls on forms or reports, or the properties of those fields or controls.

  • Operators    For example, the + (plus) or - (minus).

  • Functions    For example, SUM or AVG.

  • Constants    Values that do not change — such as strings of text, or numbers that are not calculated by an expression.

  • Values    Strings, such as "Enter a number between 1 and 10." or numbers, such as 1,254, that are used in operations.

The following sections describe these components in more detail.

Identifiers

An identifier is the name of a field, property, or control. You use an identifier in an expression to refer to the value that is associated with a field, property, or control. For example, consider the expression =[RequiredDate]-[ShippedDate]. This expression subtracts the value of the ShippedDate field or control from the value of the RequiredDate field or control. In this expression, both RequiredDate and ShippedDate serve as identifiers.

Operators

Access supports a variety of operators, including common arithmetic operators, such as +, -, * (multiply), and / (divide). You can also use comparison operators such as < (less than) or > (greater than) for comparing values, text operators such as & and + for concatenating (combining) text, logical operators such as Not and And for determining true or false values, and other operators specific to Access.

Functions

Functions are built-in procedures that you can use in your expressions. You use functions for a wide variety of operations, such as calculating values, manipulating text and dates, and summarizing data. For example, one commonly used function is DATE, which returns the current date. You can use the DATE function in a variety of ways, such as in an expression that sets the default value for a field in a table. In this example, whenever someone adds a new record, the value for the field is set to the current date by default.

Some functions require arguments. An argument is a value that provides input to the function. If a function requires more than one argument, you separate the arguments with a comma. For example, consider the DATE function in the following example expression:

This example uses two arguments:

  • The first argument is the Date() function, which returns the current date. When there are no arguments, you still need to add the function parentheses.

  • The second argument "mmmm d, yyyy", which is separated from the first argument by a comma, specifies a text string to instruct the FORMAT function how to format the returned date value. Note that the text string must be enclosed in quotation marks.

This expression also illustrates that you can often nest the value returned by one function as an argument to another function. In this case, Date() acts as an argument.

Constants

A constant is an item whose value does not change while Access is running. The True, False, and Null constants are frequently used in expressions.

Values

You can use literal values in your expressions, such as the number 1,254 or the string "Enter a number between 1 and 10." You can also use numeric values, which can be a series of digits, including a sign and a decimal point, if needed.

When you use text string values, place them within quotation marks to help make sure that Access interprets them correctly. In some circumstances, Access supplies the quotation marks for you. For example, when you type text in an expression for a validation rule or for query criteria, Access surrounds your text strings with quotation marks automatically.

To use date/time values, enclose the values in pound signs (#). For example, #3-7-17#, #7-Mar-17#, and #Mar-7-2017# are all valid date/time values. When Access encounters a valid date/time value that is enclosed in # characters, it automatically treats the value as a Date/Time data type.

Top of Page

Comparison of Access expressions and Excel formulas

Access expressions resemble Excel formulas in that both use similar elements to produce a result. Both Excel formulas and Access expressions contain one or more of the following:

  • Identifiers    In Excel, identifiers are the names of individual cells or ranges of cells in a workbook, such as A1, B3:C6, or Sheet2!C32. In Access, identifiers are the names of table fields (such as [Contacts]![First Name]), controls on forms or reports (such as Forms![Task List]![Description]), or the properties of those fields or controls (such as Forms![Task List]![Description].ColumnWidth).

  • Operators    In both Access and Excel, operators are used to compare values or to perform simple calculations on your data. Examples include + (plus) or - (minus).

  • Functions    In both Access and Excel, functions and arguments are used to accomplish tasks that you can't do by using operators alone — for example, you can find the average of the values in a field, or convert the result of a calculation to a currency format. Examples of functions include SUM and STDEV. Arguments are values that provide information to functions. Note that Access and Excel both have many functions from which to choose, but the names of similar functions in the programs are sometimes different. For example, the AVERAGE function in Excel corresponds to the AVG function in Access.

  • Constants    In both Access and Excel, constants are values that do not change — such numbers that are not calculated by using an expression.

  • Values    In both Access and Excel, values are used in a similar manner.

Access expressions use operators and constants that are similar to those used in Excel formulas, but Access expressions use different identifiers and functions. Whereas Excel formulas are generally only used in worksheet cells, Access expressions are used in many places within Access for a wide variety of tasks, including the following:

  • Creating calculated controls on forms and reports

  • Creating calculated fields in tables and queries

  • Serving as criteria in queries

  • Validating data being entered into a field, or into a control on a form

  • Grouping data in reports

You can use either an Access expression or an Excel formula to calculate numeric or date/time values by using mathematical operators. For example, to calculate a discounted price for a customer, you can use the Excel formula =C2*(1-D2) or the Access expression = [Unit Price]*(1-[Discount]).

You can also use an Access expression or an Excel formula to combine, split, or otherwise manipulate strings by using string operators. For example, to combine a first and last name into one string, you can use the Excel formula =D3 & " " & D4 or the Access expression = [First Name] & " " & [Last Name].

Top of Page

Use the Expression Builder

Build an expression

Guide to expression syntax

Examples of expressions

Sours: https://support.microsoft.com/en-us/office/introduction-to-expressions-5cad6e24-65a3-4a95-82cc-92b4b1bd4b8b
Microsoft Access 2007 2010 2013 pt 6 (Expression builder, Calculated Fields)

This article contains links to articles that provide details about common functions used in expressions in Microsoft Access. They are arranged first by group and then are arranged alphabetically at the end of this article.

Tip: Beginning with Access 2010, the Expression Builder has IntelliSense, so you can see what arguments your expression requires.

ActiveX

CreateObject Function
GetObject Function

Top of Page

Application

Command Function
Shell Function

Top of Page

Arrays

Array Function
Filter Function
Join Function
LBound Function
Split Function
UBound Function

Top of Page

Conversion

Asc Function
Chr Function
EuroConvert Function
FormatCurrency Function
FormatDateTime Function
FormatNumber Function
FormatPercent Function
GUIDFromString Function
Hex Function
Nz Function
Oct Function
Str Function
StringFromGUID Function
Type Conversion Functions
Val Function

Top of Page

Database

DDE Function
DDEInitiate Function
DDERequest Function
DDESend Function
Eval Function
Partition Function

Top of Page

Date/Time

Date Function
DateAdd Function
DateDiff Function
DatePart Function
DateSerial Function
DateValue Function
Day Function
Hour Function
Minute Function
Month Function
MonthName Function
Now Function
Second Function
Time Function
Timer Function
TimeSerial Function
TimeValue Function
Weekday Function
WeekdayName Function
Year Function

Top of Page

Domain Aggregate

DAvg Function
DCount Function
DFirst, DLast Functions
DLookup Function
DMin, DMax Functions
DStDev, DStDevP Functions
DSum Function
DVar, DVarP Functions

Top of Page

Error Handling

CVErr Function
Error Function

Top of Page

File Input/Output

EOF Function
FreeFile Function
Input Function
Loc Function
LOF Function
Seek Function

Top of Page

File Management

CurDir Function
Dir Function
FileAttr Function
FileDateTime Function
FileLen Function
GetAttr Function

Top of Page

Financial

DDB Function
FV Function
IPmt Function
IRR Function
MIRR Function
NPer Function
NPV Function
Pmt Function
PPmt Function
PV Function
Rate Function
SLN Function
SYD Function

Top of Page

Inspection

Environ Function
GetAllSettings Function
GetSetting Function
IsArray Function
IsDate Function
IsEmpty Function
IsError Function
IsMissing Function
IsNull Function
IsNumeric Function
IsObject Function
TypeName Function
VarType Function

Top of Page

Math

Abs Function
Atn Function
Cos Function
Exp Function
Int, Fix Functions
Log Function
Rnd Function
Round Function
Sgn Function
Sin Function
Sqr Function
Tan Function

Top of Page

Messages

InputBox Function
MsgBox Function

Top of Page

Miscellaneous

CallByName Function
IMEStatus Function
MacID Function
MacScript Function
QBColor Function
RGB Function
Spc Function
Tab Function

Top of Page

Program Flow

Choose Function
DoEvents Function
IIf Function
Switch Function

Top of Page

SQL Aggregate

Avg Function
Count Function
First, Last Functions
Min, Max Functions
StDev, StDevP Functions
Sum Function
Var, VarP Functions

Text

Format Function
InStr Function
InStrRev Function
LCase Function
Left Function
Len Function
LTrim, RTrim, and Trim Functions
Mid Function
Replace Function
Right Function
Space Function
StrComp Function
StrConv Function
String Function
StrReverse Function
UCase Function

Top of Page

Functions (alphabetical)

Abs Function

Array Function

Asc Function

Atn Function

Avg Function

CallByName Function

Choose Function

Chr Function

Command Function

Cos Function

Count Function

CreateObject Function

CurDir Function

CVErr Function

Date Function

DateAdd Function

DateDiff Function

DatePart Function

DateSerial Function

DateValue Function

DAvg Function

Day Function

DCount Function

DDB Function

DDE Function

DDEInitiate Function

DDERequest Function

DDESend Function

DFirst, DLast Functions

Dir Function

DLookup Function

DMin, DMax Functions

DoEvents Function

DStDev, DStDevP Functions

DSum Function

DVar, DVarP Functions

Environ Function

EOF Function

Error Function

EuroConvert Function

Eval Function

Exp Function

FileAttr Function

FileDateTime Function

FileLen Function

Filter Function

First, Last Functions

Format Function

FormatCurrency Function

FormatDateTime Function

FormatNumber Function

FormatPercent Function

FreeFile Function

FV Function

GetAllSettings Function

GetAttr Function

GetObject Function

GetSetting Function

GUIDFromString Function

Hex Function

Hour Function

IIf Function

IMEStatus Function

Input Function

InputBox Function

InStr Function

InStrRev Function

Int, Fix Functions

IPmt Function

IRR Function

IsArray Function

IsDate Function

IsEmpty Function

IsError Function

IsMissing Function

IsNull Function

IsNumeric Function

IsObject Function

Join Function

LBound Function

LCase Function

Left Function

Len Function

Loc Function

LOF Function

Log Function

LTrim, RTrim, and Trim Functions

MacID Function

MacScript Function

Mid Function

Min, Max Functions

Minute Function

MIRR Function

Month Function

MonthName Function

MsgBox Function

Now Function

NPer Function

NPV Function

Nz Function

Oct Function

Partition Function

Pmt Function

PPmt Function

PV Function

QBColor Function

Rate Function

Replace Function

RGB Function

Right Function

Rnd Function

Round Function

Second Function

Seek Function

Sgn Function

Shell Function

Sin Function

SLN Function

Space Function

Spc Function

Split Function

Sqr Function

StDev, StDevP Functions

Str Function

StrComp Function

StrConv Function

String Function

StringFromGUID Function

StrReverse Function

Sum Function

Switch Function

SYD Function

Tab Function

Tan Function

Time Function

Timer Function

TimeSerial Function

TimeValue Function

Type Conversion Functions

TypeName Function

UBound Function

UCase Function

Val Function

Var, VarP Functions

VarType Function

Weekday Function

WeekdayName Function

Year Function

Top of Page

Sours: https://support.microsoft.com/en-us/office/access-functions-by-category-b8b136c3-2716-4d39-94a2-658ce330ed83

Now discussing:

Description of the expressions to count yes, no, and other responses in Access

  • 2 minutes to read
  • Applies to:
    Access 2007, Access 2003, Access 2002, Access 2000

Note

Office 365 ProPlus is being renamed to Microsoft 365 Apps for enterprise. For more information about this change, read this blog post.

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.

Summary

This article lists sample expressions that you can use to count the occurrences of Yes, No, or Null in a field with a Yes/No data type.

More information

You can use the following expressions in a report footer to count the occurrences of Yes, No, or Null in a field named YesNoField with a data type of Yes/No:

ExpressionSums What
=Sum(IIF([YesNoField],1,0))Yes
=Sum(IIF([YesNoField],0,1))No
=Sum(IIF(Not[YesNoField],1,0))No
=Sum(IIF(IsNull[YesNoField],1,0))Null

You can also create a related expression to count a specific value in a field. For example, the following sample expression counts all occurrences of the value 3 in a field called MyField.

=Sum(IIF([MyField]=3,1,0))

Example Using Sample Database Northwind

  1. Open the sample database Northwind in Access.

  2. Use the Report Wizard to create a report based on the Products table.

  3. Select CategoryID and UnitPrice as the fields for the report.

  4. Group on CategoryID.

  5. In the design view of the report, click Sorting and Grouping on the View menu, and make sure that the GroupFooter property for CategoryID is set to Yes.

    Note In Access 2007, in the design view of the report, on the Design tab, click Group & Sort in the Grouping & Totals group, and make sure that the with a footer section property for CategoryID is selected.

  6. Add an unbound text box in the CategoryID footer section with the ControlSource property for the text box set to the following expression:

    =Sum(IIF([Discontinued],1,0))

  7. Add a second unbound text box with the ControlSource property for the text box set to the following expression:

    =Sum(IIF([Discontinued],0,1))

  8. On the File menu, click Print Preview.

    In Access 2007, click Microsoft Office Button, point to Print, and then click Print Preview.

    Notice that the first expression will count the number of products within each category that have the Discontinued field set to Yes. The second expression will count the number of products within each category that have the Discontinued field set to No.

Sours: https://docs.microsoft.com/en-us/office/troubleshoot/access/expressions-to-count-yes-no-other-reponses


431 432 433 434 435