Database Operators and Operands

Introduction to Operators and Operands

Microsoft Access and Microsoft Visual Basic are not 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.

The fields we have been using allow the user to enter, view, or change data of a database. Because there are so many types of values a user can be asked 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.

An integer is a natural number that displays without a decimal place. Examples of integers are 18, 16763, and 1450.

A byte is a small number that ranges from 0 to 255.

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. The expression “double-precision” means that this number provides a high level of precision. Valid decimal numbers are 12.55, 3.14159 or 9.80336

A date is a 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 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.

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.

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 as 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:

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.

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 a field doesn't hold a value, it is considered NULL. It is very important to understand this: the fact that a field is empty doesn't mean that it is NULL.

Microsoft Access Operators

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 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 ""

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 Parentheses Operators: ()

Value1 + Value2

= Value1 + Value2

Value1 - Value2

= Value1 - Value2

Value1 * Value2

= Value1 * Value2

Value1 \ Value2

= Value1 \ Value2

Value1 / Value2

= Value1 / Value2

yx

=y^x

Value1 Mod Value2

= Value1 Mod Value2