|
Expressions Fundamentals
Introduction to Expressions and Operations
An expression, also called an operation, is a technique
of combination of a value and an operator, two values and an operator, etc.
The value can be a known value or it can come from a field of a table. Based
on this, to create an expression or to perform an operation, you need at
least one value or field and a symbol. A value or field involved in an
operation is called an operand. A symbol involved in an operation is called
an operator.
 |
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. |
A unary operator is one that uses only one operand. An operator is referred
to as binary if it operates on two operands.
 Practical Learning: Introducing Expressions
- Start Microsoft Access
- From the resources that accompany these lessons, open the Ceil Inn2 database
- In the Navigation Pane, right-click the Employees form and
click Design View
Introduction to Constants
A constant is a value that does not change. Examples of constants are
algebraic numbers because they never change. Therefore, any number you can think of is a
constant. Every letter of the alphabet is a constant. In the same way, any
word you can think of is a constant.
Common Operators
The Assignment Operator =
In order to provide a value to an existing field, you
can use an operator called assignment and its symbol is "=".
It uses the following formula:
Field/Object = Value/Field/Object
The operand on the left side of the = operator is
referred to as the left value:
- This operand must always be able to be written to
- It cannot be a known value such as a constant
- It cannot be an expression that needs to be evaluated
- It can be the name of a table's field
- It can be an appropriate property of a field
- It can be the name of a control on a form or a report.
The operand on the right side of the operator is referred to as the right
value. It can be a constant, a value, an expression, the name of a field, or an object.
There are various ways you can use the assignment operator.
For example, in the Property Sheet for the control, in its Control Source, write an expression that assigns the existing
field. In some other cases, the assignment operator will be part of a longer
expression.
Practical Learning: Using the Assignment Operator
- On the form, click the txtHourlySalary text box in the group box
- In the Property Sheet, click the Data tab and click Control Source
- In the Record Source, type = HourlySalary:
- Save the form
Positive and Negative Values
An algebraic value is considered positive if it is greater than 0. A value
is referred to as negative if it is less than 0. To express a negative value, it must be
appended with the - symbol. Examples are -12, -448, -32706.
Besides a numeric value, the value of a field or an object can also be
expressed as being negative by typing a - sign to its left. For example, -txtLength means
the value of the control named txtLength must be made negative.
Algebraic Operators
The addition is used to add one value or expression to another. It is
performed using the + symbol. The formula to follow is:
Value1 + Value2
The multiplication allows adding one value to itself a certain number of times.
The multiplication is performed with the * symbol. The formula to follow is:
Value1 * Value2
The division is used to get the fraction of one number in terms of another
number. Microsoft Access supports two types of results for the division operation. If you want
the result of the operation to be a natural number, use the backlash "\" as the operator. The
formula to follow is:
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 second type
of division results in a decimal number. It is performed with the forward slash "/". The formula
to follow is:
Value1 / Value2
After the operation is performed, the result is a decimal number.
Practical Learning:
Performing Algebraic Operations
- On the form, click the txtMonthlySalary text box
- In the Data tab of the Property Sheet, click Control Source and type = HourlySalary * 160
- On the form, click the txtYearlySalary text box
- In the Data tab of the Property Sheet, click Control Source and type =
txtMonthlySalary * 12
- Switch the form for Form View
- Switch the form back to Design View
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.
The Remainder Operator: Mod
The division operation gives a result of a number with or without decimal
values. Sometimes you will want to get the value remaining after a division renders a natural
result. The remainder operation is performed with the Mod keyword. The formula to follow is:
Value1 Mod Value2
Microsoft Access Operators
The Period Operator: .
We already know that a property is something that characterizes or describes
an object. An example of a property is the width of a control or the text it contains. 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 formula to follow is:
Object.PropertyName
The property you are trying to use must be a valid property of the object.
The Square Brackets Operator: []
To name our objects so far, in some cases we used a name made of one word
without space. Actually, you can use spaces or some special characters in a name. Unfortunately,
when such names get involved in an expression, there would be an error or the result would be unpredictable.
To make sure Microsoft Access can recognize any name in an expression,
you can/should include it between an opening square bracket "[" and a closing square
brackets "]". Examples are [© Year], [Soc. Sec. #], or
[Date of Birth]. Even if the name is in one word, to be safe, you should (always)
include it in square brackets. Examples are [Country], [FirstName], or
[SocialSecurityNumber]. Therefore, the =txtLength expression can be written =[txtLength].
Practical Learning: Using the Square Brackets
- On the form, click one of the salary text boxes in the group box.
In the Property Sheet, notice that the square brackets were automatically
added:
- Save the form
The Collection Operator: !
The objects used in Microsoft Access are grouped in categories named collections.
For example, the forms of a database belong to a collection of objects named Forms.
The reports belong to a collection of objects named Reports. The data fields belong to a
collection named Fields. The controls on a form or report belong to a collection named
Controls
To refer to a particular object in an expression, use the exclamation
point operator "!". To do this, type the name of the collection followed by
the ! operator, followed by the name of the object you want to access. For example, on a form,
if you have a text box named txtLength and you want to refer to it, you can type
[Controls]![txtLength]. Therefore, the =txtLength expression can be written
=Controls!txtLength, and =[txtLength] can be written =Controls![txtLength] or =[Controls]![txtLength].
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 to locate the object by referring to its collection. This is useful in case two
objects of different categories are being referred to.
In a database, 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 named 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 because each object
belongs to a different collection. For this reason, when creating expressions, you should
(strongly) qualify the object you are referring to, using its collection. An example would
be Forms!Employees which means the Employees object of the Forms collection.
If the name of the form is made of more than one word, or for convenience, you must use
square brackets to delimit the name of the form. The form would be accessed with
Forms![Employees].
To refer to a control placed on a form or report, you can type the
Forms collection, followed by the ! operator, followed by the name of the form,
followed by the ! operator and followed by the name of the control. An example would be
Forms!People!LastName. Using the assignment operator that we introduced earlier,
if on a form named People, you have a control named LastName and you want to assign its
value to another control named FullName, in the Control Source property of the
FullName field, you can enter one of the following expressions:
=LastName
=[LastName]
=Controls!LastName
=[Controls]![LastName]
=Forms!People!LastName
=[Forms]![People]![LastName]
These expressions would produce the same result.
The Parentheses Operator: ()
Parentheses are used in two main circumstances: in expressions (or
operations) or in functions. The parentheses in an expression help to create sections.
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.
Introduction to Functions
Overview
A function is a task that must be performed to produce a result on a
table, a form, or a report. It is like an operation or an expression with the first difference
that someone else created it and you can just use it. For example, instead of the addition
operator "+", to add two values, you could use a function.
In Microsoft Access, you cannot create a function. You can only use those
that have been created and exist already. These are referred to as built-in functions.
|
Microsoft Access ships with various functions to
perform different tasks. There are so many of them that we will review
only a few. |
If you had to create a function (remember that we cannot create a function in
Microsoft Access; the following sections are only hypothetical but illustrative of the subject
of a function), a formula you would use is:
FunctionName()
End
This formula is very simplistic but indicates that the minimum piece of
information a function needs is a name. The name allows you to refer to this function in
other parts of the database. The name of the function is followed by parentheses. As stated
already, a function is meant to perform a task. This task would be defined or described in
the body of the function. In our simple syntax, the body of the function would start on the
line under its name and would stop one the line just above the End word. The person who
creates a function also decides what the function can do. Following our simple formula, if
we wanted a function that can open Solitaire, it could appear as follows:
FunctionExample()
Open Solitaire
End
Once a function has been created, it can be used. Using a function is
referred to as calling it. To call a simple function, you would just type its name.
A function produces a result. This is also stated that a function returns
a value. Based on this, the result of a function can be provided for further use and assigned
(passed) to a field or to another function. To display the result of a function in a field,
you can access the field's Control Source property, use the assignment operator "=",
and type the name of the function followed by parentheses. This can be done as follows:
The person who creates a function also decides what kind of value the
function can return. For example, if you create a function that performs a calculation,
the function may return a number. If you create another function that combines a first
name and a last name, you can make the function return text that represents a full name.
Arguments and Parameters
When asked to perform its task, a function may need one or more values
to work with. If a function needs a value, such a value is called a parameter. The parameter
is provided in the parentheses of the function. The formula used to create such a function
would be:
ReturnValue FunctionName(parameters)
End
Once again, the body of the function would be used to define what the
function does. For example, if you were writing a function that multiplies its
parameter by 12.58, it would appear almost as follows:
Decimal FunctionName(Parameter)
parameter * 12.58
End
While a certain function may need one parameter, another function would need
many of them. The number and types of parameters of a function depend on its goal. When a
function uses more than one parameter, a comma separates them in the parentheses. The formula used is:
ReturnValue FunctionName(Parameter1, Parameter2, Parameter_n)
End
If you were creating a function that adds its two parameters, it may appear as follows:
NaturalNumber AddTwoNumbers(Parameter1, Parameter2)
Parameter1 + Parameter2
End
Once a function has been created, it can be used in other parts of the database.
Once again, using a function is referred to as calling it. If a function is taking one or more
parameters, it is called differently than a function that does not take any parameter. We saw already
how you could call a function that does not take any parameter and assign it to a field using its
Control Source. If a function is taking one parameter, when calling it, you must provide a
value for the parameter, otherwise the function would not work (when you display the form or report,
Microsoft Access would display an error). When you call a function that takes a parameter, the
parameter is called an argument. Therefore, when calling the function, we would say that the function
takes one argument. In the same way, a function with more than one parameter must be called with its
number of arguments.
To call a function that takes an argument, type the name of the function followed
by the opening parenthesis "(", followed by the value (or the field name) that will be the
argument, followed by a closing parentheses ")". The argument you pass can be a constant
number. Here is an example:
The value passed as argument can be the name of an existing field. The rule to
respect is that, when Microsoft Access is asked to perform the task(s) for the function, the
argument must provide, or be ready to provide, a valid value. As done with the argument-less function,
when calling this type of function, you can assign it to a field by using the assignment operator in
its Control Source property.
If the function is taking more than one argument, to call it, type the values
for the arguments, in the exact order indicated, separated from each other by a comma. As for the
other functions, the calling can be assigned to a field in its Control Source.
All the arguments can be constant values, all of them can be
the names of fields or objects, or some arguments can be passed as constants and
others as names of fields.
Optional Arguments and Default Values
We have mentioned that, when calling a function that takes an argument, you must
supply a value for the argument. There is an exception. Depending on how the function was created,
it may be configured to use its own value if you fail, forget, or choose not, to provide one. This
is known as the default argument. Not all functions follow this rule and you would know either by
checking the documentation of that function or through experience.
If a function that takes one argument has a default value for it, then you don't
have to supply a value when calling that function. Such an argument is considered optional. Whenever
in doubt, you should provide a value for the argument. That way, you would not only be on the safe
side but also you would know with certainty what value the function had to deal with.
If a function takes more than one argument, some argument(s) may have default
values while some others don't. The arguments that have default values can be used and you don't
have to supply them.
The Expression Builder
Introduction
To assist you with writing an expression or calling a
(built-in) function and reducing mistakes, Microsoft Access is equipped
with a dialog box named the Expression Builder.
The Expression Builder is used to create an expression
or call a function that would be used as the Control Source of a
field.
Using the Expression Builder
To access the Expression Builder, open the Property
Sheet for the control that will use the expression or function, and click
its ellipsis button
 .
This would call the Expression Builder dialog box:
The Expression Builder is resizable: you can enlarge,
narrow, heighten, or shorten it, to a certain extent.
Under the title bar, there is the Calculated Control
link. If you click that link, a Help window would come up. Under the link,
there is an example of an expression.
The main area of the Expression Builder is a
rectangular text box used to show the current expression. If you already
know what you want, you can directly type an expression, a function, or a
combination of those.
To show a reduced height of the Expression Builder,
click the << Less button. The button would change to More >>:
To show the whole dialog box, click More >>.
Under the text box, there are three boxes. The left
list displays some categories of items. Some items in the left list appear
with a + button. To access an object, expand its node collection by
double-clicking its corresponding button or clicking its + button. After
you have e xpanded a node, a list would appear. In some cases, such as the
Forms node, another list of categories may appear.
To access an object of a collection, in the left list,
click its node. This would fill the middle list with some items that
depend on what was selected in the left list. Here is example:
The top node is the name of the form or report on
which you are working. To access a function, first expand the Functions
node. To use one of the Microsoft Access built-in functions, in the left
list, click Built-In Functions. The middle list would display categories
of functions. If you see the function you want to use, you can use it. If
the right list is too long and you know the type of the function you are
looking for, you can click its category in the middle list and locate it
in the right list.
Once you see the function you want in the right list,
you can double-click it. If it is a parameter-less function, its name and
parentheses would be added to the expression area:
If the function is configured to take arguments, its
name and a placeholder for each argument would be added to the expression
area:
You must then replace each placeholder with the
appropriate value or expression. To assist you with functions, in its
bottom section, the Expression Builder shows the syntax of the function,
including its name and the name(s) of the argument(s). To get more
information about a function, click its link in the bottom section of the
Expression Builder. A help window would display. Here is an example:
Depending on the object that was clicked in the left
list, the middle list can display the Windows controls that are part of,
or are positioned on, the form or report. For example, if you click the
name of a form in the left list,the middle list would display the names of
all the controls on that form. To use one of the controls on the object,
you can double-click the item in the middle list. When you do, the name of
the control would appear in the expression area.
Some items in the middle list hold their own list of
items. To show that list, you must click the item in the middle list. For
example, to access the properties of a control positioned on a form, in
the left list, expand the Forms node and expand All Forms:
Then, in the left list, click the name of a form. This
would cause the middle list to display the controls of the selected form.
To access the properties of the control, click its name in the middle
list. The right list would show its properties:
To get help while using the Expression Builder, you
can click Help. After creating an expression, if you are satisfied with
it, to submit it, click OK. To abandon whatever you have done, click
Cancel or press Esc.
The Calculated Field of a Table
You can create a field in a table so that the field
would hold an expression. To do this, start the table in either the
Datasheet View or the Design View:
- In the Datasheet View
- Click Click to Add, position the mouse on Calculated Field,
and select one of the options:
- Click a cell under the desired column. In the Add & Delete
section of the Ribbon, click More Fields, postion the mouse on
Calculated Field and select one of the options
- In the Design View, specify the name of the new field. Set its
Data Type to Calculated
In both cases, the Expression Builder would come up.
You can then type or create the desired expression in the top text box.
|
Practical
Learning: Creating Message Boxes
