Microsoft Access Lessons Home

Database Operators and Operands

 

Introduction to Operators and Operands

Neither Microsoft Access nor Microsoft Visual Basic is case-sensitive. Therefore, any word we are going to use that involves a field, its name, and new words we will introduce in this section, whether written in uppercase, lowercase or a mix, as long as it is the same word, represents the same thing. Based on this, the words TRUE, True and true, as related to Microsoft Access, represent the same word. In the same way, if the words NULL, Null, and null are used in an expression, they represent the same thing.
 

Introduction to Data Types

The fields we have been using allow the user to enter, view, or change data of a database. Because there is so many types of values a user can be ask to deal with, the values in the fields are categorized by types. In the next lesson, we will see how to specify what type of value should or can be entered in a field and what type of value should be prevented. At this time, we will review the categories of data that are considered in a database.

A string is any word or group of words considered as an entity.

A Boolean value is one that can be expressed in only one of two values. For example, either it is 1 or it is 0, either it is true or it is false, either it is yes or it is no. This type of value is represented with the Boolean data type.

An integer is a natural number that displays without a decimal place. Examples of integers are 18, 16763, and 1450. An integer is expressed with the Integer data type. If the number is very large, then it must be represented by the Long data type.

A byte is a small number that ranges from 0 to 255. It is represented using the Byte data type.

A double, also called double precision, value is a number that can display a decimal portion, using the character set as the decimal separator in Control Panel. For US English, that character would be the period. A double-precision number is expressed with the Double data type. The expression “double-precision” means that this number provides a high level of precision. If you are dealing with a decimal number but precision is not important, then the number can be represented by the Single data type. Valid decimal numbers are 12.55, 3.14159 or 9.80336.

A date is a numeric value that counts the number of days that have elapsed from a certain point of reference. How a date displays in a field is based on some conventions set by Microsoft Access, Microsoft Windows, or you. There are also rules you must respect. Examples of dates are 28/06/1998, 10-8-82, January 10, 1865.

A time is a numeric value that counts the number of seconds that have elapsed since midnight of a certain day. The time also displays following some conventions set in Control Panel, by Microsoft Access, or the person who created the database. Examples of time values are 10:42 AM and 18:06.

A currency is a numeric type used to represent money value in a field. A money value is represented using the Currency data type.

Overview of Operators and Operands

The data fields we have used so far were created in tables and then made available to other objects, queries, forms, and reports, so those objects can implement their own functionality without worrying about displaying empty or insignificant fields. In various scenarios, you will need to display a field that is a combination of other fields. For example, you may need to combine a FirstName to a LastName fields in order to create a FullName field, or, to calculate an employee’s weekly salary, you may need to retrieve the value of a Salary field and multiply it with the value of a total number of hours worked in a week. Most, if not all, of these expressions use what we call operators and operand.

An operation is a technique of using a value or a field, or to combine two or more values or data fields to either modify an existing value or to produce a new value. Based on this, to perform an operation, you need at least one value or field and one symbol. A value or field involved in an operation is called an operand. A symbol involved in an operation is called an operator.

A unary operator is one that uses only one operand. An operator is referred to as binary if it operates on two operands.

Constants

 

Introduction

A constant is a value that does not change. The constants you will be using in your databases have already been created and are built-in Microsoft Access. Normally, Visual Basic for Applications (VBA), the version of Microsoft Visual Basic that ships with Microsoft Access also provides many constants. Just in case you are aware of them, you will not be able to use those constants, as Microsoft Access does not inherently “understand” them. For this reason, we will mention here only the constants you can use when building regular expressions.

The algebraic numbers you have been using all the time are constants because they never change. Examples of constant numbers are 12, 0, 1505, or 88146. Therefore, any number you can think of is a constant. Every letter of the alphabet is a constant and is always the same. Examples of constant letters are d, n, c. Some characters on your keyboard represent symbols that are neither letters nor digits. These are constants too. Examples are &, |, @, or !

True or False

In Boolean algebra, something is considered True when it holds a value. The value is also considered as 1 or Yes. By contrast, if something does not hold a value, it is considered non-existent and not worthy of consideration. Such a thing has a value of False, 0, or No. In reality, everything in your database has a value, as far as the database engine is concerned. To retrieve such a value, you can just find out if the value of a field is existent or not.

The comparison for a True or False value is mostly performed on Boolean fields, such a case is the Single Parent field of the student registration objects (table, form, possibly query, and report) of the ROSH database. If a record has a check mark, the table considers that such a field is True. If the check box is not checked, then it holds a False value.

NULL

While the True and False constants are used to find out whether a check box is marked or not, the database provides another constant used to find out whether a field is empty. This can be done using the Null constant.

When a field holds a value, the value would be considered using the comparison operators we will learn shortly. If the field is empty, it holds a value of NULL.

Operators

 

The Assignment Operator =

In order to provide a value to an existing field, you can use an operator called assignment. Its syntax is:

VariableName = ValueOrVariable

The operand on the left side of the = operator is referred to as the left value or Lvalue. This operand must always be able to be written to. In other words, it cannot be a type of constant we will review next. The operand on the right side of the operator is referred to as the right value or Rvalue. It can be a data field or a constant.

There are two main ways we will use the assignment operator. Imagine you already have a field and you want to display the value of that field in another field. In the Control Source of the new field, you can write an expression that assigns the existing field. Here is an example that assigns the value of an existing FirstName field to a new field using the Control Source property:

In some other cases, the assignment operator will be part of a longer expression. We will see examples we move on.

The Positive Unary Operator +

Algebra uses a type of ruler to classify numbers. This fictitious ruler has a middle position of zero. The numbers on the left side of the 0 are considered negative while the numbers on the right side of the 0 constant are considered positive:

-∞   -6 -5 -4 -3 -2 -1   1 2 3 4 5 6   +∞
   0
-∞   -6 -5 -4 -3 -2 -1   1 2 3 4 5 6   +∞

A value on the right side of 0 is considered positive. To express that a number is positive, you can write a + sign to its left. Examples are +4, +228, +90335. In this case the + symbol is called a unary operator because it acts on only one operand. The positive unary operator, when used, must be positioned to the left side of its operand.

As a mathematical convention, when a value is positive, you do not need to express it with the + operator. Just writing the number without any symbol signifies that the number is positive. Therefore, the numbers +4, +228, and +90335 can be, and are better, expressed as 4, 228, or 90335. Because the value does not display a sign, it is referred as unsigned.

The Negative Unary Operator -

As you can see on the above ruler, in order to express any number on the left side of 0, it must be appended with a sign, namely the - symbol. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative. The - sign must be typed on the left side of the number it is used to negate.

Remember that if a number does not have a sign, it is considered positive. Therefore, whenever a number is negative, it must have a - sign. In the same way, if you want to change a value from positive to negative, you can just add a - sign to its left. In the same way, if you want to negate the value of a field and assign it to another field, you can type the – operator on its left when assigning it.

The Square Brackets Operator: []

When reviewing name rules for our objects in Microsoft Access, we saw that we should use names that are made of one word (no space). In reality, Microsoft Access, as mentioned already, is particularly flexible with names. It uses a mechanism to delimit a name when such a name is involved in an operation. The operator used to specify the beginning of a name is the left or opening square bracket [. At the end of a name, a right or closing bracket is used. To be safe, whether a name is made of one or more words, Microsoft Access prefers including it in square brackets.

In an operation, also called an expression, instead of using such a name as FirstName, you should use [FirstName]. Of course, if a name is made of more than one word, which is frequent on database objects created using the Database Wizard, you must always include it in square brackets. An example would be [Video Titles Subform]. The field we used earlier to assign a FirstName value to another can be written as follows:

The Collection Operator: !

The objects used in Microsoft Access are grouped in categories called collections. For example, the forms belong to a collection of objects called Forms. The reports belong to a collection of objects called Reports. The data fields belong to a collection called Controls. Based on this, all forms of your database project belong to the Forms collection, all of your reports belong to the Reports collection, and all controls you are using belong to the Controls collection.

To call a particular object in an operation, using the exclamation point operator, type the name of the collection followed by the ! operator, followed by the name of the object you need to use. For example, if you have a text box called FirstName and you want to refer to it, you can type Controls!FirstName. Here is the same example as earlier that assigns a FirstName field to a text box using its Control Source property:

The name of the collection is used to perform what is referred to as qualification: the name of the collection “qualifies” the object. In other words, it helps the database engine locate the object by referring to its collection. This is useful in case two objects of different categories are being referred to. Since you cannot have two fields with the same name on the same form or report, it is safe to omit the Controls name of the collection when referring to a field.

Whether you use the Database Wizard or you create your own objects, Microsoft Access allows two objects to have the same name, as long as they do not belong to the same category. For example, you cannot have two forms called Employees in the same database. In the same way, you cannot have two reports named Contracts in the same database. On the other hand, you can have a form named Employees and a report named Employees in the same database. For this reason, when creating expressions, you should (with emphasis) qualify the object you are referring to, using its collection. Therefore, when an object named Employees is referred to in an expression, you should specify its collection, using the ! operator. An example would be Forms!Employees which means the Employees form of the Forms collection. If the name of the form is made of more than one word, or for convenience (strongly suggested), you must use square brackets to delimit the name of the form. Therefore, the form would be access with Forms![Employees].

To refer to a control placed on a form or report, you can add the name of the control at the end of the expression. Because the control belongs to its collection, it can be accessed with the ! operator:

The Period Operator: .

In previous lessons, we learned that a property is something that characterizes or describes an object. For example, users mainly use a text box either to read the text it contains, or to change its content, by changing the existing text or by entering new text. Therefore, the text the user types in a text box is a property of the text box. To access the property of an object, type the name of the object, followed by a period, followed by the name of the property you need. The syntax used is:

Object.PropertyName

The property you are trying to use must be a valid property of the object. In Microsoft Access, to use a property of an object, you must know, either based on experience or with certainty, that the property exists. Even so, unfortunately, not all properties are available.

Double Quotes ""

A string is an empty space, a character, or a group of characters that you type or provide to a control and you want this character or this group of characters to be considered "as is". In other words, the expression or the control that receives the string should keep it or them the way you supplied it or them, unless you specify otherwise.

A string can be an empty space or one character, such as $ or w; a group of characters, like home or Manchester United or Verbally speaking, I mean… Ah forget it. Most of the time, you will want the database to keep this character or group of characters exactly the way you or the user supplied them. In order to let the program know that this is a string, you must enclose it in double quotes. From our examples, our strings would be "$", "w", "home", "Manchester United", and "Verbally speaking, I mean… Ah forget it". To assign a string to a field, in the Control Source of the field, you can type the assignment operator followed by the double-quoted string. Here is an example:

The String Concatenator: &

The & operator is used to add one string to another. It can also be used to append the contents of two controls, or expressions. This is considered as concatenating them. For example, it could allow you to concatenate a first name and a last name, producing a full name. The general syntax of the concatenation operator is:

Value1 & Value2

To display a concatenated expression in an unbound field, use the assignment operator on the field. Here is an example:

Here is an example of the form in Form View:

Once again, you should include the name of a field in square brackets. To concatenate more than two strings, you can use as many & operators between any two expressions as necessary. After concatenating the strings or values, you can assign the result to another value or expression using the assignment operator. The syntax used is:

=Field1 & Field2 & Field_n

Here are examples

=FirstName & " " & LastName

This would display, for example, Boniface Dunkirk

=[LastName] & ", " & [FirstName]

This would produce, for example, Chang, Helene

=[Address] & " " & [City] & " " & [State] & " " & [ZIPCode] & " " & [Country]

This would display a complete address in a field

The Addition: +

The addition is used to add one value or expression to another. It is performed using the + symbol and its syntax is:

Value1 + Value2

The addition allows you to add two numbers such as 12 + 548 or 5004.25 + 7.63

After performing the addition, you get a result. You can provide such a result to another field of a form or report. This can be done using the assignment operator. The syntax used would be:

= Value1 + Value2

The Subtraction: -

Subtraction is performed by retrieving one value from another value. This is done using the – symbol. The syntax used is:

Value1 - Value2

The value of Value1 is subtracted from the value of Value2. After the operation is performed, a new value results. This result can be used any way you want. For example, you can display it in a control using the assignment operator as follows:

= Value1 - Value2

The Multiplication: *

Multiplication allows adding one value to itself a certain number of times, set by the second value. The multiplication is performed with the * sign which is typed with Shift + 8. Here is an example:

Value1 * Value2

During the operation, Value1 is repeatedly added to itself, Value2 times. The result can be assigned to the Control Source of a field as. The expression would be written as follows:

= Value1 * Value2

The Integer Division: \

Dividing an item means cutting it in pieces or fractions of a set value. For example, when you cut an apple in the middle, you are dividing it in 2 pieces. If you cut each one of the resulting pieces, you will get 4 pieces or fractions. This is considered that you have divided the apple in 4 divisions. Therefore, the division is used to get the fraction of one number in terms of another.

Microsoft Access provides two types of results for the division operation. If you want the result of the operation to be a natural number, called an integer, use the backlash operator "\" as the operator. Here is an example:

Value1 \ Value2

This operation can be performed on two types of valid numbers, with or without decimal parts. After the operation, the result would be a natural number. The result of the operation can be assigned to a field using the assignment operator:

= Value1 \ Value2

The Division: /

The second type of division results in a decimal number. It is performed with the forward slash "/". Its syntax is:

Value1 / Value2

After the operation is performed, the result is a decimal number. The result of the operation can be assigned to a field using the assignment operator:

= Value1 / Value2

The Exponentiation: ^

Exponentiation is the ability to raise a number to the power of another number. This operation is performed using the ^ operator (Shift + 6). It uses the following mathematical formula:

yx

In Microsoft Access, this formula is written as y^x and means the same thing. Either or both y and x can be values or expressions, but they must carry valid values that can be evaluated.

When the operation is performed, the value of y is raised to the power of x. You can display the result of such an operation in a field using the assignment operator as follows:

=y^x

The Remainder Operator: Mod

The division operation gives a result of a number with or without decimal values, which is fine in some circumstances. Sometimes you will want to get the value remaining after a division renders a natural result. The remainder operation is performed with keyword Mod. Its syntax is:

Value1 Mod Value2

The result of the operation can be used as you see fit or you can display it in a control using the assignment operator as follows:

= Value1 Mod Value2

The Parentheses Operators: ()

Parentheses are used in two main circumstances: in procedures or in operations. The parentheses in an operation help to create sections in an operation. This regularly occurs when more than one operators are used in an operation. Consider the following operation: 8 + 3 * 5.

The result of this operation depends on whether you want to add 8 to 3 then multiply the result by 5 or you want to multiply 3 by 5 and then add the result to 8. Parentheses allow you to specify which operation should be performed first in a multi-operator operation. In our example, if you want to add 8 to 3 first and use the result to multiply it by 5, you would write (8 + 3) * 5. This would produce 55. On the other hand, if you want to multiply 3 by 5 first then add the result to 8, you would write 8 + (3 * 5). This would produce 23.

As you can see, results are different when parentheses are used on an operation that involves various operators. This concept is based on a theory called operator precedence. This theory manages which operation would execute before which one; but parentheses allow you to control the sequence of these operations.

Logical Operators

 

Introduction

Databases and other programming environments provide operators you can use to perform data analysis. The operators used are called logical operators because they are used to perform comparisons that produce a result of true or false (there is no middle result: something is not half true or half false or "Don't Know"; either it is true or it is false).

The Equality =

The equality operator is used to compare two values for similarity. The syntax of this operation is:

Value1 = Value2

If Value1 and Value2 hold the same value, then the comparison produces a True result. If they hold different values, the comparison renders a False value

Equality

Inequality <>

To find out if two fields hold different values, you can use the inequality operator which is represented by <>. Its syntax is:

Value1 <> Value2

This comparison is performed between Value1 and Value2. If they hold different values, then the comparison produces a True value (make sure you understand this property of Boolean algebra). If they hold the same value, the comparison produces False:

Inequality

This shows you that the equality (=) and the inequality (<>) operators are opposite each other.

Less Than <

The "Less Than" operator uses the following syntax:

Value1 < Value2

If Value1 holds a value that is lower than that of Value2, the comparison produces True. If Value1 holds a value that is greater or similar to that of Value2, the comparison renders false:

Less Than

Less Than Or Equal <=

When comparing two values, you may want to know whether two fields hold the same value or if one is lower than the other. This comparison can be performed with the "Less Than Or Equal To" operator. It is represented by <=. Its syntax is:

Value1 <= Value2

If both operands (Value1 and Value2) hold the same value, then the comparison produces a True result. If Value1 holds a value that is lower than that of Value2, the comparison still produces a True result. By contrast, if the value of Value1 is higher than that of Value2, the comparison renders a False result:

Less Than Or Equal

Note that the > and the <= operators are opposite each other.

Logical Operators: Greater Than >

The > operator is used to find out whether one value is "Greater Than" another. Its syntax is:

Value1 > Value2

The operation is performed on the values of Value1 and Value2. If Value1 holds a value greater than that of Value2, then the comparison produces True. Otherwise, the comparison produces False. That is, if the value of Value2 is greater than or equal to that of Value1, then the comparison produces False:

Greater Than

Greater Then Or Equal >=

If you have two values and want to find out whether they hold similar values or the first is greater than the second, you can use the >= operator whose syntax is:

Value1 >= Value2

If both Value1 and Value2 hold the same value, then the comparison renders a True result. Similarly, if the left operand, Value1, holds a value greater than that of the right operand, Value2, the comparison still produces True. If the value of Value1 is less than the value of Value2, the comparison produces a False result:

Greater Than Or Equal

Therefore, < and >= are opposite.

The NOT Operator

To deny the presence, the availability, or the existence of a value, you can use the Not operator. This operator is primarily used to reverse a Boolean value. For example, we have learned that False is the opposite of True. In the same way, True is the opposite of False. If you want to compare a value as not being True, the Not True expression would produce the same result as the False value. For the same reason, the expression Not False is the same as True.

The IS Operator

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. In the same way, to validate that something is not null, you can use the expression Is Not NULL.

The IN Operator

The IN operator is used to check whether a record or a group of records is found in a group.

 

Previous Copyright © 2002-2007 Yevol Next