Introduction to 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.

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.

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. A possible syntax would be:

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:

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 4^1/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")

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

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 to know 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 in 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 function 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.

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 pseudo-code for this function is:

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

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

MOUS Topics