Introduction to Data Expressions

Expressions Fundamentals   Introduction to Expressions and Operations The data fields we have used so far were created in tables and then made available to other objects (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 first name to a last name fields in order to create a full name 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 expression, also called an operation, is a technique of combining two or more values or data fields, to either modify an existing value or to produce a new value. Based on this, to create an expression or 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. Practical Learning: Introducing Expressions Start Microsoft Access Open the College Park Auto Repair1 database from the previous lesson In the Navigation Pane, under Forms, right-click RepairOrders and click Design View On the form, click Parts Used and click its body to select that tab Introduction to Constants 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 !. The names of people are constants too. In fact, any name you can thing of is a contant. 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 syntax: 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. Imagine you already have a field named FirstName and you want to display the value of that field in another field named FullName. To do this, open the Property Sheet for the FullName text box and, in its Control Source, you can write an expression that assigns the existing field as =txtLastName. This would be done as follows: In some other cases, the assignment operator will be part of a longer expression. We will see examples we move on. Positive and Negative Values An algebraic value is considered positive if it is greater than 0. 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. A value is referred to as negative if it is less than 0. To express a negative value, 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. 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 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 To use the result of this type of operation, you can write it in the Control Source property of the field that would show the result. 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.  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 division is used to get the fraction of one number in terms of another number. 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 "\" 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 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 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   Microsoft Access Operators   The Period Operator: . In previous lessons, we learned that a property was something that characterized 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 in Microsoft Access. The Square Brackets Operator: [] To name our objects so far, in some cases we used a name made of one word without space. In some other cases, we used spaces or special characters in a name. This is possible because Microsoft Access allows a great level of flexibility when it comes to names used in a database. 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 should include it between an opening square bracket "[" and a closing square brackets "]". Examples are [© Year], [Soc. Sec. #], or [Date of Birth]. In the same way, 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 that we referred to can be written =[txtLength]. Practical Learning: Creating Expressions In the Parts Used section of the form, double-click the first Unbound text box under Sub Total to access its Property Sheet In the Property Sheet, click the Data tab. Click Control Source and type =[UnitPrice1]*[Quantity1] On the form, click the second text box under Sub-Total. In the Property Sheet, click Control Source and type =[UnitPrice2]*[Quantity2] On the form click the third text box under Sub-Total. In the Property Sheet, click Control Source and type =[UnitPrice3]*[Quantity3] On the form, click the fourth text box under Sub-Total. In the Property Sheet, click Control Source and type =[UnitPrice4]*[Quantity4] On the form click the fifth text box under Sub-Total. In the Property Sheet, click Control Source and type =[UnitPrice5]*[Quantity5] Save the form 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. Consequently, all forms of your database project belong to the Forms collection. The reports belong to a collection of objects called Reports and all reports of your database belong to the Reports collection. The data fields belong to a collection called Controls and all controls of a form or a report of your database belong to the Controls collection. To call 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 called txtLength and you want to refer to it, you can type [Controls]![txtLength]. Therefore, the =txtLength expression that we referred to 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 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. 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 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 (strongly) 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, you must (strongly suggested) 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 Operators: () 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; but parentheses allow you to control the sequence of these operations.

 

Expressions and Functions   Introduction 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 practicality, you cannot create a function in Microsoft Access. You can only use those that have been created and that 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 syntax 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 just under its name after the parentheses and would stop 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 like the above FunctionExample, 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 its Control Source property, use the assignment operator "=", type the name of the function, followed by its parentheses. This would 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 a string 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(Parameter) 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 syntax used is: ReturnValue FunctionName(Parameter1, Parameter2, Parameter_n) End If you were creating a function that adds its two parameters, it would 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 parenthesis ")". 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 will be 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. Here is an example: 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. Here is an example: 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 do not have to supply a value when calling that function. Such an argument is considered optional. Whenever in doubt, you should provide your own 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 do not. The arguments that have default values can be used and you do not have to supply them.  The Expression Builder   Introduction To assist you with writing expressions or calling a (built-in) function and reduce the likelihood of a mistake, Microsoft Access is equipped with a good functional 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 Like every regular dialog box, the Expression Builder starts on top with its title bar that displays its caption and its system Close button. Unlike a regular 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 a label followed by a link: Calculated Control. If you click that link, a Help window would come up: Under the link, there is an example of an expression. The main upper area of the Expression Builder shows a rectangular text box with a white background. It is used to show the current expression when you have written it. If you already know what you want, you can directly type an expression, a function, or a combination of those. The right section of the Expression Builder displays a few buttons. After creating an expression, to submit it, you click OK. To abandon whatever you have done, yo can click Cancel or press Esc. To get help while using the Expression Builder, you can click Help. 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 expanded 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, you can click its node. This would fill the middle list with some items that would of course 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. Under that name are the Functions node. 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: Besides the built-in functions, if you had created a function in the current database, in the left list, click the name of the database, its function(s) would display in the middle list. 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 an 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: As mentioned already, after creating the expression, if you are satisfied with it, click OK. Practical Learning: Using the Expression Builder The Repair Orders form of the College Park Auto Parts1 database should still be opened in Design View. On the form, click the Order Summary tab and click it again to make sure it is selected Click the Total Parts text box (the Unbound text box on the right side of Total Parts:) In the Property Sheet, click the Data tab. Click Control Source and click its ellipsis button In the left list, double-click Functions to expand it In the left list, double-click College Park Auto Parts to expand it Double-click Forms Double-click Loaded Forms In the left list, click RepairOrders In the middle list, scroll down and double-click SubTotal1 In the expression area, click the right side of the closing square bracket and type + [SubTotal2] + [SubTotal3] + [SubTotal4] + [SubTotal5] Click OK On the form, click the TotalLabor text box In the Property Sheet, click Control Source and type =[JobPrice1] + [JobPrice2] + [JobPrice3] + [JobPrice4] + [JobPrice5] Save the form  Close the RepairOrders form Close Microsoft Access