Introduction to Expressions
Overview of Expressions
An expression is a combination of data fields, operators, values, and/or procedures destined to produce a new value. There are various types of expressions you will be using in your applications. When creating an expression, you would ask the database engine to supply data of a specific field following your recommendation. Data you specify is usually not provided by a field on a table. Instead, you can create a field that is a combination of fields from a table, a form, a query, or a report. The data of the expression can also be the result of a combination of dependent fields or values external to any table, form, query, or report.
There are two main things you will use to create your expressions: operators and functions. As we saw in the past, an operator is a symbol (or a character) that is applied to one or more items to produce a new value. As we will learn later on, a function is an assignment that accomplishes a specific and isolated job then gives back a result.
To create an expression, you use any combination of the operators we have used so far. Once the expression is ready, you can assign it to a field on a form or report. To do that, while in Design View, you can access the Control Source of the field in its Properties window, type the assignment operator “=”, followed by the expression. For example, imagine you want to create an expression as 126.55 + 42.808. To display the result of this expression in the text box of a form, in the Control Source of the text box, you can type = 126.55+42.808 and press Enter
When such a form displays in Form View, the field that holds the expression would display the result (provided it can successfully get the result). Later on, we will see other techniques of creating an expression.
As we will see in this and later chapters, the various functions available in Microsoft Access make it a valuable application to create good functional databases. Nevertheless, Microsoft Access is not a programming environment but a good platform to visually create databases. As your experience grows, you will face some demands that become difficult to achieve. For this reason, Microsoft Access ships with a
computer language called Visual Basic For Applications or VBA. Although in this book we will not explore computer programming, we will take a glimpse at what some of the code lines look like.
You may recall that when we created subforms, we selected only a few fields from the data source. This was purposely done so the user can have the handiest pieces of information about the data that the current form is related to. While the user is performing data entry, he may want to have more details about the data that is displaying. You can achieve this by calling the originating form. To implement this functionality, you can create a command button and let Microsoft Access write code that would take you straight to the related record only.
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 an argument. While a certain function may need one argument, another function would need many arguments. The number and types of arguments of a procedure depend on its goal.
ReturnValue FunctionName(Argument) 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 argument by 12.58, it would appear almost as follows:
Decimal FunctionName(Argument) Argument * 12.58 End
While one function may use only one argument, another function may use more than one, depending on its goal. When a function uses more than one argument, a comma separates them. The syntax used is:
ReturnValue FunctionName(Argument1, Argument2, Argument_n) End
If you were creating a function that adds its two arguments, it would appear almost as follows:
NaturalNumber AddTwoNumbers(Argument1, Argument2) Argument1 + Argument2 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 arguments, it is called differently than a function that does not take any argument. We saw already how you can call a function that does not take any argument and assign it to a field using its Control Source. Here is an example:
If a function is taking one argument, when calling it, you must provide a value for the argument, otherwise the function would not work (when you display the form or report, Microsoft Access would display an error).
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 regular constant value or it 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. Here is an example:
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.
There are three main ways you create expressions. If you know, based on experience, what the expression is made of, you can just write it in the Control Source of the field that will hold the expression. This technique is faster but prone to error. Creating an expression is a technique of giving a "made-up" value to a field. The field that would hold such an expression is referred to as unbound because it is not directly linked to any field of a table. Since the new value that constitutes the expression is in fact "assigned" to the expression-field, you must use the assignment operator which is "=". Therefore, the first symbol you will use in an expression is =.
When you acquire more experience with Microsoft Access, you will be able to perform some operations very quickly with little or no help. Nevertheless, to assist you with creating expressions, Microsoft Access, continuing with its limitless number of wizards, provides a special dialog box for this specific purpose. This is the role of the Expression Builder. To access it, click the Control Source field in the Properties window, which would reveal its ellipsis button . Then click the ellipsis button . This would call the Expression Builder dialog box
Once the Expression Builder displays, you can include an operator by clicking its corresponding button. You can also type the symbol of the operator using your keyboard. To select a field that already exists in the current form or report, in the left list, you can click the first item. To access a field that exists in another object, you can first double-click a category (Tables, Queries, Forms, or Reports) in the left list. This would display the list of objects of that category. Then you can click the object itself. To actually include a field in your expression, you can double-click it in the middle list.
To use one of the built-in functions of Microsoft Access, in the left list, you can double-click the Functions node to expand it. Then click Built-In Functions. The categories of functions would display in the middle list. The real functions appear in the right list. To see the syntax of a function, you can simply click it once. Its syntax would appear in the bottom section of the Expression Builder dialog box:
Without being particularly explicit, the syntax of the function allows you to know the number and order of arguments of a function. To get more information about the function, you can check its documentation in the help files.
To actually include the desired function in your expression, you can double-click its name in the right list. If you already know the name of the function, you can still manually type it in the desired section of the expression.
When creating an expression, if you click the button of an operator, the <<expr>> would display as a placeholder, indicating that a value is expected:
If you double-click the name of a function in the right list, if the function takes one or more arguments, a placeholder would be created for each argument:
You have the responsibility to supply an appropriate value for each placeholder. To do that, you have two alternatives. If you know the value that should be used in a placeholder, you can just type it. Alternatively, you can use the operator buttons or the built-in functions to fill a placeholder. We should point out that not all functions are available in the list of functions of the Expression Builder but, as long as you know that a Visual Basic function is supported and you know its syntax, you can use it.
To ease your job with database implementation, Microsoft Access ships with many functions. These functions have been tested and are highly reliable, so much that in this book, we will use only the built-in functions. Because there are so many of them, they are classified in categories and you are not likely to use all or even most of them.
The users of your database will be presented with fields they can fill to either provide new values or change the existing values. Anything the user types in a field is primarily considered a string. Before performing any type of operation that involves such a value, you should make sure you can identify what kind of value it is. For example, you should not try to multiply a string by a date such as FirstName * January 16. Although you will not be able to avoid every single type of problem that could occur in your database, you can reduce errors by checking the value that a control holds.
In the previous lesson, we mentioned that a form could be divided in various sections separated by a page break. Although such sections are created in the Design View of a form, Microsoft Access does not inherently provide a way to access them. You have two alternatives. You can use a macro or VBA. For one thing we will not use macros in this book. For another, we will afford to write one line of code to apply the functionality we need.
On a form, to access a section that was separated with a page break, you can call the GoToPage() function (in reality, GoToPage() is a “method” of the DoCmd “class”). The GoToPage() method takes as a required argument the number that corresponds to the section you are trying to access.
The first action you should take when dealing with the value retrieved from a field is to convert it to the appropriate type. There are various conversion functions adapted to the different possible kinds of values. The general syntax of the conversion functions is:
ReturnType = FunctionName(Expression)
The expression could be of any kind. For example, it could be a string or value the user would have entered in a form. It could also be the result of a calculation performed on another field or function. The conversion function would take such a value, string, or expression and attempt to convert it. If the conversion is successful, the function would return a new value that is of the type specified by the ReturnType in our syntax.
In the strict sense, because Microsoft Access is not a traditional programming environment, it does not provide a feature called conditional or control statement, which is used to check a condition. Instead, it provides functions you can use to check that a condition is true or false. There are various functions that can be used to create an expression as complex as an algorithm would allow. It is important to note that, although most of these functions perform conditional checking, they do not return the same type of value. For this reason, you should choose the right function. Most of these functions will also be used in combination with other functions as necessary.
The most regularly used function to perform conditional statements is called IIf. The Immediate If function, IIf(), needs three pieces of information in order to accomplish its assignment. The syntax used is:
IIf(Condition, WhatToDoIfConditionIsTrue, WhatToDoIfConditionIsFalse)
The first action this function performs is to evaluate a condition. This condition could involve an operation or the result of an operation. If the Condition is true, then the function would execute the first expression, which is identified in our syntax as WhatToDoIfConditionIsTrue.
The Condition could lead to only one of two results. For example, the Condition could consist of checking whether an employee is married or not. If the field is a combo box, it might have only a Yes and a No values. In that case, the user would select only either Yes or No. A Condition could also result in more than two values. For example, suppose a combo box holds the book categories of a database. Such a combo box could display values such as History, Entertainment, Biographies, Computers, Computer Programming, Geography, and Artificial Intelligence. When a field can hold various values, the Condition you specify as the first item of your IIf() function would be checked. If it produces a true result, the WhatToDoIfConditionIsTrue statement would be executed. If the Condition renders false, the WhatToDoIfConditionIsFalse statement would be executed. This is important to keep in mind: the IIf() function checks only one condition. If you want to check more than one condition, you would have to write a more elaborate expression.
Imagine that you have a list of students and you want to take some action depending on a student being a boy or a girl. You can pose a condition that would check whether the field that holds the student's gender is displaying Male or Female. The IIf() function used here could be =IIf(Gender = "Male", WhatToDoIfMale, WhatToDoIfNotMale). Imagine that a combo box holds the values of M and F. If you have a text box named txtGender that would display a gender string depending on the value selected from a combo box named cboGender, in the Control Source of the text box, you can write the IIf() function as follows:
If the field contains a value Return that value Otherwise Return 0
|Previous||Copyright © 2002-2007 Yevol||Next|