Operators and Expressions in Microsoft Access 2013

Expression Builder
Expression Builder.

To really maximize the results of queries and calculations from Microsoft Access, users need to become familiar with operators and expressions as early as possible. Understanding what each of these elements of Access is and how they work will give you much more reliable results for whatever task you are completing. From more accurate calculations to targeted searches or queries, operators and expressions are two of the fundamental building blocks for getting the most out of Access.

Operators are the signs and symbols that indicate what type of calculations Access should use for a particular expression. They serve a number of different purposes, such as mathematical or comparative, and the symbols range from a plus sign or division symbol to words, such as And, Or, and Eqv. There is also a special class of operators that are generally associated with coding, such as Is Null and Between…And.

Expressions are more complex than operators and are used to execute a number of different tasks in Access. They not only provide calculations; expressions can extract, combine, compare, and validate data. They are very powerful, and so it may take some time to fully understand how and when to use them.

Types of Operators

The following details the five types of operators and how you use them.

Arithmetic operators are the type of operator most people think of when they hear the term calculations. They calculate the value of at least two numbers or change a number to either positive or negative. The following details all of the arithmetic operators:

+                Addition

-                 Subtraction

*                Multiplication

/                Division

\                Round to the nearest integer, divide, then truncate to an integer

^                Exponent

Mod        Divide, and then show only the remainder

Comparison operators are perhaps the most common for databases as the primary purpose of a database is to review and analyze data. The following are the comparison operators, and the result indicates the relation of the first value to the other data. For example, < indicates that the first value is less than the second value in the comparison.

<                Less than

<=             Less than or equal to

>                Greater than

>=             Greater than or equal to

=                Equal to

<>             Not equal to

Null         Either the first or second value is null because comparisons cannot include unknown values.

Logical operators, or Boolean operators, analyze two Boolean values and result in true, false, or null.

And         Returns results when both expressions are true

Or             Returns results when either of the expressions is true

Eqv          Returns results when either both expressions are true or both expressions are false

Not          Returns results when the expression is not true  

Xor          Returns results when only one of the two expressions is true

Concatenation operators combine text values into a single value.

&              Creates one string from two strings

+                Creates one string from two strings, including a null value when one of the strings is null

Special operators result in a True or False response.

Is Null/Is Not Null     Analyzes if a value is Null

Like…                                Finds string values matching the entry after Like; wildcards help widen the search                

Between …                   Compares values to the specified range after Between

In(…)                                  Compares values to see if they are within the specified range in parentheses

Relationship Between Operators and Expressions

You have to understand operators to create expressions. While operators really don’t have any application on their own, they can be a very powerful tool if used correctly in an expression. For example, a plus sign on its own does not really do anything because there are no values for it to add. However, when you create a mathematical equation (called an expression in Access), 2+2, you not only have values but you can get a result as well. Expressions require at least one operator, just as you do not have an equation without the plus sign.

For those familiar with Microsoft Excel, an expression is the same thing as a formula. Expressions follow a similar structure, regardless of type, just as a formula or equation always follows a structure no matter how complex it is.

All field and control names are contained within their own set of brackets. While Access will sometimes create the brackets for you (when you enter only one name without spaces or special characters), it is best to get in the habit of adding the brackets.

When to Use an Expression

Expressions can be used nearly anywhere within Access, including reports, tables, forms, and queries. For the advanced users, expressions can be used in macros to consistently pull data for regular analysis. They can be used to convert currency, calculate the total spent on a project or contributions made, or even to compare the money spent on different projects to determine which project was the most effective. The more you learn about expressions, the easier it is to understand when it would be simpler to create one for regular use instead of exporting data to a spreadsheet or doing the work manually.

How to Create an Expression

Access has an Expression Builder that will do the work for you, so even as you get accustomed to the different operators and possible uses for expressions you can create them quicker.

To access the builder, right click on the object (table, form, report, or query) you want to use the expression on, then go into the Design View. Depending on the object, use the following instructions.

Table – click on the field you want to change, then the General tab. Select the property where you want to add the expression, then the Build button (three ellipses).

Forms and reports – click on the control, then Properties. Select the property where you want to add the expression, then the Build button (three ellipses).

Query – click on the cell where you want to add the expression (remember you should be looking at the design grid, not a table). Select Query Setup from the Design tab, then Builder.

It will take some time to get accustomed to creating expressions, and a sandbox can be really helpful so that you don’t save experimental expressions in a live database.