Lessons Logo

Introduction to Expressions

 

Overview of Expressions

 

Introduction

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.

Algebraic Expressions

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.

So far, we have seen that, data provided to a form is controlled by the Data Source property. Therefore, when creating an expression for a form's field, you must supply the expression to the Data Source field of the Properties window. Probably the simplest expression you can create is by transferring the value from an existing field to another field. Imagine you have a field from a form and the field is named MailingAddress. If you want the value of the MailingAddress field to be transferred or assigned to a field named ShippingAddress, in the Control Source of the ShippingAddress field, you would type =MailingAddress. You can also include the name of a field in square brackets as we saw when studying operator. Instead of MailingAddress, it would consider it as [MailingAddress]. If you were assigning a field to another, you would write the assignment as =[MailingAddress].

Suppose you have a field named HourlySalary and another field named WeeklyHours. If you want to calculate the weekly salary of an employee, you would multiply the HourlySalary by the WeeklyHours and assign the result to another field. In the Control Source property of the resulting field, you would type =[HourlySalary]*[WeeklyHours]

   

Practical Learning: Basic Expressions
  1. Open the GCS application you created
  2. Open the OrderProcessing form:
     
  3. After viewing the form, switch it to Design View
  4. Click the Unbound field under the left Total that corresponds to the Pants
  5. In the Properties window, click Control Source and type
    = QtyPants * PricePants
     
  6. Press Enter. Notice that Microsoft Access adds the square brackets in the expression to become 
    =[QtyPants]*[PricePants]
  7. Switch the form to Form View to preview it and switch it back to Design View
  8. Click the Unbound text box on the right side of the Order Total label In the Control Source of the Properties window, type the following:
      
    =[txtPants]+[txtMenSuit2]+[txtMenSuit3]+[txtSweater]+
[txtRegularShirt]+ [txtSilkShirt]+[txtTie]+[txtOther1]+[txtWomenSuit]+
[txtDresses]+ [txtRegularSkirt]+ [txtSkirtHook]+[txtCoat]+[txtJacket]+
[txtSwede]+[txtOther2]
  9. Click the Unbound text box on the right side of the Change label
  10. In the Properties window, click Control Source. Type
      
    = [txtAmountTended] – [txtTotalOrder] and press Enter
  11. Preview the form:
     
  12. Save and close it. Click the Customer text box (the text box on the right side of the Customer: label)

VBA and Expressions

 

Introduction

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. 

Automatically Generated Code

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.

Practical Learning: Generating VBA Code
  1. Open the Rockville Techno1 database and, in the Database window, click Forms
  2. Double-click the Clients form to open it
  3. After viewing the form, switch it to Design View
  4. On the Toolbox, make sure the Control Wizard button is clicked
    To generate code that closes the form, on the Toolbox, click the Command Button and click an empty area in the Form Footer section to the right of the Preview Invoice button
  5. On the first page of the Command Button Wizard, in the Categories list, click Form Operations. In the Actions list, click Close Form and click Next
  6. In the text box, delete Form to have only Close and click Next
  7. Change the name to cmdClose and click finish
  8. Resize and reposition the buttons as follows:
     
  9. To see the code that Microsoft Access had generated, right-click the Close button you just added and click Build Event…
     
  10. Close the window or Visual Basic to return to Microsoft Access
  11. Switch the form to Form View. To close it, click the Close button you created
  12. When asked whether you want to save it, click Yes

Procedures and Functions

 

Introduction to Procedures and Functions

A procedure is a task that must be performed aside of a table, a form, or a report, to complement it. It is like an operation or an expression with the first difference that someone else created it and you can just use it. Microsoft Access ships with various procedures to perform different tasks. There are so many of them that we will review only those that are most regularly used.

If you had to create a procedure, one of the syntaxes you would use is:

ProcedureName()

End

This syntax is very simplistic but indicates that the minimum piece of information a procedure needs is a name. The name allows you to refer to this procedure in other parts of the database. In reality, a procedure is meant to perform a task. This task would be defined or described in the body of the procedure. In our simple syntax, the body of the procedure would start just under its name and would stop just above the End word. The person who creates a procedure also decides what the procedure can do. Following our simple syntax, if we wanted a procedure that can open Solitaire, it could appear as follows:

ProcedureExample()
Open Solitaire
End

Once a procedure has been created, it can be used. Using a procedure is referred to as calling it. To call a simple procedure like the above ProcedureExample, you would just type its name.

The type of procedure we have referred to so far in this section is usually called a sub procedure. Most of the procedures we will use in Microsoft Access are called functions. As opposed to a sub procedure, a function would use the syntax:

ReturnType FunctionName()

End

The main difference, as you may see here is that 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, a sub procedure, or 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.

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.
Because most, if not all, of the built-in procedures you will use in Microsoft Access are functions, from now on, we will use only the word “function” but this refers to both types of procedures.
 

Procedures, Functions, and Their Arguments

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.

The arguments of a function are provided in parentheses. The syntax used to create such a function would be:

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:

Default Arguments

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

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.

Introduction to Built-In Functions

 

Overview

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.

Page Break References

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.

Conversion Functions

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.

The conversion functions are as follows:

Function  
Name Return Type Description
CBool Boolean Converts an expression into a Boolean value
CByte Byte Converts an expression into Byte number
CDate Date Converts and expression into a date or time value
CDbl Double Converts an expression into a flowing-point (decimal) number
CInt Integer Converts an expression into an integer (natural) number
CCur Currency Converts an expression into a currency (monetary) value
CLng Long Converts an expression into a long integer (a large natural) number
CSng Single Converts an expression into a flowing-point (decimal) number
CStr String Converts an expression into a string
 

Practical Learning: Converting Data Values
  1. Open the College Park Auto Shop2 database and click Forms in the Database window
  2. Double-click the sbfParts subform to open it
  3. After viewing the subform, switch it to Design View
  4. Widen the form so its right border touches the 41/4 mark of the horizontal ruler
  5. On the Toolbox, click the Label and click on the right side of the Qty label in the Form Header section. Type Total and press Enter
  6. Make it appear Bold like the other labels
  7. On the Toolbox, click the Text Box control and click in the Detail section
  8. Delete its label
  9. Using the Properties window, change the following properties for the new unbound text box:
    Name: txtSubTotal
    Control Source: =CDbl([UnitPrice]) * CInt([Quantity])
    Format: Currency
    Decimal Places: 2
    Top: 0.02
  10. Save the subform and close it
     

Logical Functions

 

Introduction

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.

Immediate If

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: 

=IIf([cboGender]="M","Boy","Girl")

Practical Learning: Using IIf
  1. Open the Bethesda Car Rental1 database
  2. Open the Customers1 form in Design View
  3. Click the middle tab
  4. On the Toolbox, click Text Box 
  5. Click on top of the DriversLicNbr text box
     
  6. Change the label's caption to Full Name:
  7. Click the new text box to select it
  8. On the Properties window, change its name to txtFullName and press Enter 
  9. In the Control Source, type (in one line)
      
    =IIf([MI]="",[FirstName] & " " & [LastName],[FirstName]
 & " " & [MI] & " " & [LastName])
  10. Switch to Form View. Click the Driver's License tab and navigate through a few records, those whose customer has a middle initial and those that do not have a middle initial
  11. Save and close the form

 Field Emptiness

The conversion functions we reviewed above are effective if they are supplied a value to convert. In some of your expressions, you will provide a data field as argument to use by a function. Even if you decide to convert a field to the appropriate value, you may need to first check whether the field is empty. This can be taken care of by the IsEmpty() function. Its syntax is:

IsEmpty(Value)

This function checks its argument, which could be a field or an expression. If it is a field and the field is empty, this function returns True. If the field is not empty, the IsEmpty() function returns False.

Practical Learning: Checking For Empty Field
  1. The Bethesda Car Rental1 database should still be opened.
    Reopen the Customers1 form in Design View and click the Driving Information tab
  2. On the form, click the txtFullName text box and, in the Properties window, change the value of the Control Source to (in one line)
      
    =IIf(IsEmpty([MI]),[FirstName] & " " & [LastName],[FirstName]
 & " " & [MI] & " " & [LastName])
  3. Switch the form to Form View and check the values of different records in the Driving Information tab
  4. Save and close the form

Nullity Checking

Another problem you may encounter when involving a field in an operation is whether the field has never contained a value. This operation is sometimes confused with that of checking whether a field is empty. Here is the difference (it is important to understand this because it is used in many other environments, including Microsoft Visual Basic and Microsoft SQL Server):

  • Imagine a field is used for first name and the field displays Paul. If the user comes to that record, the field is not empty, it already contains a name, which is this case is Paul. If the user clicks in the field and deletes Paul, the field becomes empty. It is not null
  • Imagine a field is used for first name. If the user comes to a new record, the field for the first name may be empty (if you did not give it a default value). In this case, the field is Null: it is not empty because it has never contained anything. If the user types a name, and then deletes it, the field is not considered Null anymore: it has become empty

To check whether a field (or a value) is null, you can call the IsNull() function. Its syntax is: 

IsNull(Value)

Also used on fields, the IsNull() function checks the state of a field (remember, this functions does not check whether a field is empty or not; it checks if the field has ever contained a value). If the field it null, this function returns True. If the field is not null, this function returns False.

Numerical Checking

When the word operation is stated, most people think of a numeric value, an operator, and another numeric value. Such values must be valid in order for the operation to succeed. It certainly would not make sense to multiply a DateHired by a HourlySalary. Therefore, before involving a value or field to an operation, you should check that it contains a valid number. To perform this checking, Microsoft Access provides the IsNumeric() function. Its syntax is:

IsNumeric(Value)

The IsNumeric() function returns True if its argument is a valid number. Otherwise, it returns False.

Practical Learning: Checking for Numeric Value
  1. Open the College Park Auto Shop2 database and click Forms in the Database window
  2. Right-click the sbfParts subform and click Design View
  3. Click the text box under the Total label and, in the Data tab of the Properties window, change the value of the Record Source as follows:
    =CDbl([UnitPrice])*IIf(IsNumeric([Quantity]),CInt([Quantity]),0)
  4. Save and close the subform

Non-Zero Checking

We saw that, before performing any operation on the value held by a field, you should first convert it to the appropriate value. The conversion functions we mentioned assume that the field contains a value. Unfortunately, it is not always the case. Microsoft Access provides the Nz() function that can be used to check whether a field contains a value, whether the field is empty, or is Null. The syntax of this function is:

Nz(Value, ValueIfNull)

The first argument, Value is required. The second argument is optional. The pseudocode for this function is:

 
If the field contains a value
    Return that value
Otherwise
    Return 0

In other words, this function considers the value provided as the Value argument. This could be an expression but it is usually used on a field that is involved in a calculation. If the Value argument or the passed field is empty, this function returns 0. If the field contains a value, this function returns. The beauty of this function is that it provides a valuable safeguard for an operation.

The second argument is optional and it would be used as the return value if the Value argument were null.

Practical Learning: Basic Expressions
  1. Open the Gcs database and open the OrderProcessing form in Form View
  2. After viewing it, switch it to Design View
  3. Click the first text box under the left Total that corresponds to the Pants
  4. On the Properties window, change the value in the Control Source to =Nz([QtyPants])*Nz([PricePants])
  5. Do the same for each of the text boxes under each Total label
  6. Save and close the form

 

Lesson Summary
 

MOUS Topics
 
S20 Use a Calculated Control on a form
S31 Create a calculated field

 

 
Previous Copyright © 2002-2007 Yevol Next