Introduction to Expressions and Functions

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

1. Start Microsoft Access
2. From the resources that accompany these lessons, open the Ceil Inn2 database
3. 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

1. On the form, click the txtHourlySalary text box in the group box
2. In the Property Sheet, click the Data tab and click Control Source
3. In the Record Source, type = HourlySalary:

4. 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

1. On the form, click the txtMonthlySalary text box
2. In the Data tab of the Property Sheet, click Control Source and type = HourlySalary * 160
3. On the form, click the txtYearlySalary text box
4. In the Data tab of the Property Sheet, click Control Source and type = txtMonthlySalary * 12
5. Switch the form for Form View

6. 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

1. 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:

2. 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.

Introduction to Built-In Functions

Character Retrieval

In order to create expressions that complete a database as we saw in previous lessons, you can use various functions available from Microsoft Access. These are referred to as built-in functions.

The Chr() function is used to retrieve a character based on an ASCII character number passed to the function. It could be used to convert a number to a character. It could also be used to break a line in a long expression. The syntax of this function is:

`Chr(Number)`

Based on the table of ASCII characters, a call as Chr(65) would produce the letter A. Not all ASCII characters produce a known letter. For example, when Chr(10) is used in a string, it creates a "new line".

The Input Box

Although most of the user's data entry will be performed on fields positioned on a form, you can display a special form to the user and expect a value. This form (actually a dialog box) is called an input box. To support this, Microsoft Access provides a function called InputBox. The basic syntax of the InputBox() function is:

`InputBox(prompt)`

To use an input box, you can access the event of a control in the Property Sheet and type the code as we will see. As an alternative, after accessing the event of the control, click its ellipsis button, which would display the Expression Builder. In the left list, expand the Functions node and expand Built-In Functions. In the middle list, click InputBox and notice its syntx in the bottom part:

The required argument passed to the InputBox() function is a message that would be displayed to the user. When the input box comes up, a form with a message and a text box would display to the user.

To display an input box to the user, place a control, such as a Button, to a form. Then, access the Property Sheet for the control. In the Property Sheet, use an appropriate field, such as On Click from the Events tab, and assign the function to the field. An example would be:

`=InputBox("Enter the Radius of the Circle")`

When the user clicks the button, an input box would display:

When an input box comes up, it does not display a caption in the title bar. If you want, you can display a title of your choice. To do this, pass a second argument to the InputBox() function. Here is an example:

`=InputBox("Enter the Radius of the Circle","Geometric Figures")`

This would produce:

When an input box comes up and you present a request, the user may not know what type of value you are expecting. To guide the user, you can provide a sample or default value. This value would display in the text box and it would be selected so the user can type to replace it.

To provide a default value to the input box, you can pass a third argument to the InputBox() function. Here is an example:

`=InputBox("Enter the Radius of the Circle","Geometric Figures", 10.5)`

When the input box is called from this function, it would display as follows:

The Message Box

Introduction

A message box is a Windows (operating system)-created form used to display some information to the user. As opposed to a regular form, the user cannot type anything on the box. There are usually two ways you can use a message box: you can simply display a message to, or request an answer from, the user.

Creating a Message Box

To create a message box, you have various options.

To create a message box using a macro, after starting a new macro:

• In the left window, click the arrow of the combo box and select MessageBox
• In the right window, expand Actions and expand User Interface Commands. Drag MessageBox to the left window

To manually create a message box, use a function called MsgBox. If you only want to display a message, the syntax you would use is:

`MsgBox(Message To Display)`

This function takes only one required argument, the message to display. If you are creating the message box using a macr, type the message in the Message text box. If you are manually typing the code, pass it to the parentheses of the function. The message can be passed between double-quotes.

To display a message to the user, place a command-based control, such as a Button, to a form. Then, access the Property Sheet for the control. In the Events tab of the Properties, click On Click and assign the function to the field. An example would be:

`=MsgBox("Remember to submit your time sheet")`

When the user clicks the button, a message box would come up:

If you want to display the message box on various lines, edit the string to include a call to Chr(10). Here is an example:

```MsgBox("Remember to submit your time sheet" + Chr(10)
"Only time sheets received on time will be honored", )```

This would produce:

The message to display can also come from another control on the form. For example, you can retrieve the value of a control and pass it as argument to the MsgBox() function.

The simplest message box displays only a message to the user, with one button marked OK. If you want, you can display more than one button on the message box. To support this, you can bed the following syntax of the MsgBox() function:

`MsgBox(Message to display, Options)`

The second argument allows you to specify the button(s) to display. To do this, pass a constant integer as argument from the following table:

 Value Buttons 0 1 2 3 4 5

Here is an example:

`=MsgBox("Do you want to submit your time sheet?",4)`

This would produce:

If you provide a value other than those in the list, the message box would display only the OK button.

Besides displaying a button, the second argument is also used to display an icon. To get an icon, you add one of the following values:

 Value Icon Suited when 16 Warning the user of an action performed on the database 32 Asking a question that expects a Yes or No, or a Yes, No, or Cancel answer 48 A critical situation or error has occurred. This icon is appropriate when informing the user of a termination or deniability of an action 64 Informing the user of a non-critical situation

To use one of these icons, add (a simple addition) its value to that of the desired button or combination of buttons from the previous table. Here is an example created with

`=MsgBox("Do you want to submit your time sheet?", 4 + 32)`

This is the same as:

`=MsgBox("Do you want to submit your time sheet?", 4 + 32)`

This would produce:

When the buttons of a message box display, if the message box has more than one button, one of them has a thick border. This button is referred to as the default button. If the user presses Enter, such a button would be activated. Besides selecting the buttons and displaying an icon, the second argument can also be used to specify what button would have focus, that is, which one would have a thick border and would be applied if the user presses Enter. The default button is specified using one of the following values:

 Value If the message box has more than one button, the default button would be 0 The first button 256 The second button 512 The third button 768 The fourth button

To specify this option, add the number to the second argument.

When a message box comes up, it displays a title as Microsoft Office Access. If you want, you can specify your own title. To support this, you can pass a third argument to the MsgBox() function. This third argument is referred to as the caption. The syntax to use is:

`MsgBox(Message to display, Options, Caption)`

Pass the third argument as a string, for example in double-quotes. Here is an example:

```=MsgBox("Do you want to submit your time sheet?",
4 + 64,
"Georgetown Cleaning Services")```

This would produce:

Because MsgBox is a function, you can retrieve the value it returns and use it as you see fit. The value this function returns corresponds to the button the user clicked on the message box. Depending on the buttons the message box is displaying, after the user has clicked, the MsgBox() function can return one of the following values:

 If the button(s) displayed was(were) And if the user clicked The MsgBox() function returned 1 1 2 3 4 5 6 7 2 6 7 4 2

Practical Learning: Creating Message Boxes

The purpose of this exercise is to let you monitor the sequence of events of a form (whiat evnt comes after which one?)

1. On the Ribbon, click File and click New
2. Click Blank Desktop Database
3. Set the file name as Exercise2 and click Create
4. On the Ribbon, click Create
5. In the Macros & Code section, click Macro
6. Click the arrow of the combo box and select MessageBox
7. In the Message text box, type A record has been accessed
8. In the Type combo box, select Information
9. In the Title text box, type Microsoft Access Form Events

10. Close the macro window
11. When asked whether you want to save, click Yes
12. Set the name to mcrCurrent and press Enter
13. In the same way, create new macros as follows (use the same Type = Information and the same title = Microsoft Access Form Events for all of them):

 Message Name The form is loading to occupy the computer memory. mcrLoad The form is opening. mcrOpen The form is closing. mcrClose The form is resizing itself to signal its borders. mcrResize The form has been activated and is now the front runner. mcrActivate The form is either being closed or being sent behind another window. mcrDeactivate The form is unloading to be removed from the computer memory. mcrUnload
14. On the Ribbon, click Create
15. In the Forms section, click Form Design
16. Double-click the button at the intersection of the rulers
17. In the Property Sheet, click Event
18. Click On Current and type mcrCurrent
20. Click On Open and type mcrOpen
21. Click On Close and type mcrClose
22. Click On Resize and type mcrResize
23. Click On Activate and type mcrActivate
24. Click On Deactivate and type mcrDeactivate
26. Close the form
27. When asked whether you want to save, click Yes
28. Set the name to FormEvents and click OK
29. In the Navigation Pane, double-click the FormEvents form
30. Watch the sequence of message boxes and click OK on each
31. Close the form
32. Close Microsoft Access