Introduction to Data Expressions

Expressions Fundamentals

Introduction to Expressions and Operations

Neither Microsoft Access nor Microsoft Visual Basic is case-sensitive. Therefore, any word we are going to use that involves a field, its name, and new words we will introduce in this section, whether written in uppercase, lowercase or a mix, as long as it is the same word, represents the same thing. Based on this, the words TRUE, True and true, as related to Microsoft Access, represent the same word. In the same way, if the words NULL, Null, and null are used in an expression, they represent the same thing.

1. Start Microsoft Access and, to create a new database, click Blank Database
2. Set the File Name to Georgetown Dry Cleaners1 and click Create
3. On the default table, double-click ID and type CleaningOrderID
4. Double-click Add New Field, type Customer Name and press Tab
5. Type Customer Phone
6. Complete the columns as follows:
    

   CleaningOrderID

   Customer Name

   Customer Phone

   Deposit Date

   Retrieve Date

   Unit Price Shirts

   Quantity Shirts

   Unit Price Pants

   Quantity Pants

   Unit Price Other

   Quantity Other

7. To save the table, right-click the Table1 tab and click Save
8. Set the name to Cleaning Orders and press Enter
9. Right-click the Cleaning Orders tab and click Close
10. In the Navigation Pane, click Cleaning Orders: Table
11. To create a new form, on the ribbon, click Create and, in the Forms section, click Form
12. Press Ctrl + S to save the form
13. Accept the suggested name of the form and click OK
14. Right-click the Cleaning Orders tab and click Design View
15. Click an unoccupied area in the Detail section to make sure no control is selected.
    In the Detail section, click the top-left section and drag to the lower-right section to draw a rectangle that covers all controls
16. When the controls are selected on the form, to ungroup them, on the ribbon, click the Arrange tab. In the Control Layout section, click the Group button and click the Ungroup button
17. Design the form as follows (you will add 4 text boxes named, from top to bottom: txtSubTotalShirts, txtSubTotalPants, txtSubTotalOther,  and txtCleaningTotal):
     
    
18. Save the form

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.

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 Properties window 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

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

1. On the form, double-click the Unbound text box on the right side of the Quantity shirt to access its Properties window
2. On the Properties window, click the Data tab.
   Click Control Source and type =[Unit Price Shirts]*[Quantity Shirts]
3. On the form click the second text box under Sub-Total.
   In the Properties window, click Control Source and type
   =[Unit Price Pants]*[Quantity Pants]
4. On the form click the third text box under Sub-Total.
   In the Properties window, click Control Source and type
   =[Unit Price Other]*[Quantity Other]
5. 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.

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.

Introduction

In the above sections, we saw what operators could be used to create an expression. We also had a glimpse of what a function looked like and how it could be called from the Properties window. In those sections, we saw how to manually create an expression and how to manually call a function. 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.

Using 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. Therefore, to access the Expression Builder, open the Properties window 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, its help button, 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 and to the left, the expression area, with a large white background, 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 in the expression area.

Under the expression area, there are various buttons each displaying an operator. To use an operator in your expression, you can click its button. What happens depends on what was already in the expression or what button you clicked. For example, if you first click = and then click &, another section would be added to the expression. Also, to assist you with creating an expression, sometimes when you have clicked a button, Microsoft Access would add «Expr». This is referred to as a placeholder: it indicates to you that you must replace «Expr» with a valid value, a valid expression, or a valid function call.

Under the bar of buttons, there are three list boxes. The left list displays some categories of items. The top node is the name of the database on which you are working. Under the name of the current database are the names of collections of items of a database. The Tables node represents the Tables collection and it holds the names of the tables of the current database. In the same way, the Forms name represents the Forms collection of a database, and it holds the names of the forms of the current database. If there are no items in a collection, the node would display only a yellow button. If there is at least one item in the category, its node would display a + button.

To access an object of your database, 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:

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, in the middle list, 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:

To use an item from the right list into an expression, you can either click the item and click the Paste button, or you can double-click the item in the right list

Based on these descriptions, to access one of the Microsoft Access built-in functions, in the left list, expand the Functions node and 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 for you but 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 either click it and click Paste or 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) (unfortunately, this help is not particularly helpful, for example, it shows neither the return type nor the type of each argument).

The top-right section of the Expression Builder displays a few buttons. If you make a mistake after adding an item to the expression area, you can click Undo. To get help while using the Expression Builder, you can click Help. To dismiss the dialog box, you can click Cancel.

After creating the expression, if you are satisfied with it, click OK.

Practical Learning: Using the Expression Builder

1. The Cleaning Orders form of the Georgetown Dry Cleaner1 database should still be opened in Design View.
   On the form, double-click the last Unbound text box to access its Properties window
   In the Properties window, click the Data tab. Click Control Source and click its ellipsis button
2. Click the = button
3. In the left list, double-click Functions to expand it
4. Click Built-In Functions
5. In the middle list, click Conversion
6. In the right list, scroll down and click Nz (in future lessons, we will learn what that function does)
7. Click the Paste button
8. In the expression area, select the contents of the parentheses: «expr», «valueifnull»
    
   
9. In the left list, double-click Forms and double-click Loaded Forms to expand them
10. In the left list, click Cleaning Orders
11. In the middle list, scroll down and double-click txtSubTotalShirts
     
    
12. In the expression area, click on the right side of the closing parenthesis and click the + button
13. Following the above 4 to 11 steps for the txtSubTotalPants and the txtSubTotalOther fields, complete the expression
     
    
14. Click OK and close the Properties window
     
    
15. Save the form
16. To preview the form, right-click the Cleaning Orders tab and click Form View
17. To see the results, create the following records:
     

    Customer Name	Shirts		Pants		Other
    	Unit Price	Qty	Unit Price	Qty	Unit Price	Qty
    Carl Arndt	1.15	4	1.85	2	0.00	0
    April Drasco	0.00	0	1.95	1	3.25	3
    Christie Hanson	1.15	1	1.85	4	4.50	2
    Amie Perlman	0.00	0	0.00	0	3.55	5

     
    
18. Close the Cleaning Orders form