Chapter 13: Using Expressions in Queries and VBA Code
In this chapter, you gain a more complete understanding of expressions and functions. You have already seen and used expressions and functions in queries and forms in earlier chapters. This chapter focuses on more advanced expressions, and also discusses some of the most common built-in functions of Access. Built-in functions are very powerful in queries, forms, reports, and the VBA editor’s Immediate window.
This chapter uses the database named Chapter13.accdb. If you have not already copied it onto your machine from the CD, you need to do so now.
Understanding Expressions
Generally speaking, an expression is the means used to explain or model something to someone or something. An expression has a value—the expression 2 + 2 has a value of 4. Most expressions you use in Access have a value that is used by an Access application in place of a literal or simple numeric value.
In computer terms, an expression is generally composed of a combination of symbols, operators, numbers, strings, or identifiers that represent a numeric or string value. The expression is a representative object that Access uses to interpret something and, based on that interpretation, to obtain specific information. Simply put, an expression is a term or series of terms controlled by operators. Expressions are a fundamental part of Access operations. They are used to perform a calculation, manipulate characters, or test data.
You use expressions in Access to accomplish a variety of tasks. The same expression may serve as a WHERE clause in a SQL statement, as a filter for a form or report, or in VBA procedure. Expressions establish criteria for a query, or filter, control macros, and perform as arguments in user-defined functions.
Access evaluates an expression each time it is used. An expression used in a form or report is re-evaluated every time the form refreshes (as when changing records) to ensure accuracy of the results. If an expression is used as a criterion in a query, Access evaluates the expression every time the query is executed, thereby ensuring that the criterion reflects changes, additions, or deletions to records since the last execution of the query. If an expression is used in the table design as a validation rule, Access evaluates the expression each time the field’s value is modified to determine whether the value is allowed in the field. Expressions used as table validation rules may include references to multiple fields within the table, whereas expressions used as field-level validation rules reference only that field’s value.
To give you a better understanding of expressions, consider these examples:
= RTrim(LastName) & “ “ & RTrim(LastName)
= (Price-(Price * tblSalesLineItems.DiscountPercent)) < 25000
Taxable = True
ContactType = “Buyer” And State = “MA”
Sales.SalesDate Between 6/1/2007 And 4/30/2008
Each is a valid expression. Access can use them as validation rules, query criteria, calculated controls, control sources, and control-source properties. Some expressions use built-in functions like RTrim() in the first example. RTrim removes all spaces from the right side of the string passed as an argument (in this example, LastName and FirstName).
Figure 13-1 shows a query (qrySimpleExpression) that uses a simple expression in the first field of the returned data. Notice that it has concatenated (joined) the FirstName and LastName fields as a single value named BuyerFullName.
Figure 13-1 illustrates a special feature of Access queries. That is, the ability to create columns in the query’s returned data that is derived from data in the data sources underlying the query. In this case, the query involves only two tables, and the derived field (BuyerFullName) is generated from two columns in the Contacts table.
Figure 13-1
A simple query using an expression. Notice that the expression has a name attached to it: ‘Buyer Full Name’.
The parts of an expression
As the examples in the preceding section demonstrate, expressions can be simple or complex. Expressions can include a combination of operators, object names, functions, literal values, and constants.
Remembering that expressions don’t need to contain all these parts, you should understand each of the following uniquely identifiable portions of an expression:
Operators: >, =, *, And, Or, Not, Like, and so on.
Operators indicate what type of action (operation) will be performed on one or more elements of an expression. Generally speaking, operators are either mathematical or Boolean (true/false) in nature.
Object names: Forms![frmContacts], LastName, Price, tblProducts.Description
Object names, also known as identifiers, are the actual objects: tables, forms, reports, controls, or fields.
Functions: Date(), DLookUp(), DateDiff()
Functions always return a value. The resulting value can be created by a calculation, a data conversion result, or an evaluation. You can use a built-in Access function or a user-defined function that you create.
Literal values: 100, Jan. 1, 2007, “Seller”, “[A-D]*”
These are actual values that you supply to the expression. Literal values can be numbers, strings, or dates. Access uses the values exactly as they are entered.
Constants: Yes, No, Null, True, False
Constants represent values that do not change.
The following example illustrates the parts of an expression:
[FollowUpDate] = Date() + 30 where:
[FollowUpDate] is an object name or identifier, perhaps the name of a control on a form or a field in a recordset.
= is an operator.
Date() is a built-in VBA function.
+ is an operator.
30 is a literal.
Figure 13-2 shows a simple form (frmSimpleExpression) bound to a query named qryContactsAndContactLog. Notice the two data entry controls—Contacted On and Follow-up date. These controls are automatically populated with today’s date (Contacted On) and 30 days from now (Follow-up Date). If the user leaves these controls empty when adding a new record, the form automatically adds the default values (today’s date, and 30 days from now, respectively).
Figure 13-2
Using code that uses expressions to add field values to a record
Figure 13-3 shows the code that contains the expressions to check values automatically in the fields in Figure 13-3.
Figure 13-3
The code used for updating the fields in Figure 13-2
Examining the code in Figure 13-3, you can see many expressions. A couple of them are chkFollowUp, IsNull(txtDateContacted), and IsNDateContacted = Date. Notice that all three of these use the word Me with the field name. Me is a keyword that references the current parent object (form, report, or class module). It is only used in Visual Basic for Applications code.
This chapter talks a lot about identifiers and names. Perhaps a little clarification is in order before we go too far in our discussion of expressions. An identifier is a reference to an object or to a value. For instance, I may point to a particular car in the parking lot and say, “That is my car.” “My car” is an identifier and refers to one and only one car in the parking lot. If I’ve actually given a name to my car, I may say something like “The name of my car is Pokey.” Once I’ve provided a name for the car, I can refer to it as either “my car” or “Pokey,” and you’ll know what I mean.
In Access, you might refer to a form as Forms!frmContacts (the exclamation operator (!) is explained later in this chapter). Forms!frmContacts is an identifier that points to a particular form in the application. If instead, you wrote a VBA statement to open the same form, you might write the statement like this:
DoCmd.OpenForm “frmContacts”
In this case, frmContacts is the name of the form. It’s confusing because you use the same word as an expression and as a name, but the context in which the word is used is important. The general rule is that names are surrounded by quotation marks (“) and identifiers may or may not be surrounded by square brackets ([]), as explained later in this chapter.
Creating an expression
Expressions are commonly entered in Property windows, macro action arguments, and query criteria grids. Expressions are often used within VBA statements as well. As you enter an expression into a property box or grid cell, the text in the area shifts to the left so that you can continue to enter the expression. Although it is easy to enter an expression in this manner, it is often desirable to see the entire expression as you enter it. This is especially true when you are working with long, complex expressions. Access has a Zoom box (opened with Shift+F2) that shows even the longest expressions (see Figure 13-4).
Figure 13-4
The Zoom box makes it easy to see long expressions.
As you enter expressions, Access may insert certain characters for you when you change focus. Access checks your syntax and automatically inserts these characters:
• Brackets ([]) around identifiers that have no spaces or punctuation in the name. Brackets are much like quote marks (“) and help Access understand the beginning and end of an identifier’s name.
• Pound signs (#) around date values.
• Quotation marks (“ “) around text that contains no spaces or punctuation in the body.
The term changing focus refers to the movement of the insertion point out of the location where you are entering the expression, which is accomplished by pressing Tab or by moving the mouse and clicking another area of the screen.
Access reports an error when it changes focus when Access doesn’t understand the date you entered, when the name of the control contains spaces, when a control is not placed in brackets, when an end parenthesis is missing in a function, and on and on.
Very often you have to manually enter the square brackets, quotation marks, or pound sign delimiters around identifiers to make sure Access understands what you mean.
Entering object names
Object names are identified by placing brackets ([]) around the element. Although the brackets are usually optional, Access requires the use of brackets when the object contains a space or punctuation in its name (like a dash). If these conditions are not present, you can ignore the brackets—Access inserts them automatically. The following expressions are syntactically identical:
Buyer & [Sales Person ID]
[Buyer] & [Sales Person ID]
The field name SalespersonID was changed in the previous example to Sales Person ID; placing spaces between the names demonstrates how to use brackets around the field name when it contains spaces. The example databases on this book’s CD do not use spaces in object names.
Notice that in both cases the brackets are placed around Sales Person ID because this object name contains spaces.
Many Access developers routinely include square brackets around identifier names. Brackets almost never cause problems and may help explain an identifier. Although it isn’t necessary to enter brackets around objects such as Buyer in the second example, it is good programming practice to always surround object names with brackets for consistency in entry.
Entering text
Quotation marks around an element in an expression identify literal text. Access automatically places the quotation marks for you if you forget to add them (that is, as long as Access can figure out what you mean).
As an example, you can enter Buyer and Both into separate criteria cells of a query, and Access automatically adds the quotation marks around each of these entries. Access recognizes these as object names and helps you out by adding the quotation marks. Figure 13-5 illustrates how this works. Access automatically added the quotation marks around Buyer in the Criteria cell in the ContactType column.
Figure 13-5
Access adds the quotation marks around the query’s criterion if it can tell what you mean.
Entering date/time values
Pound signs (#) around a date/time element identify date/time data. Access evaluates valid date/time formats and places the pound signs around the element for you.
All the following expressions are recognized as date/time values by Access, and all mean May 13, 2009 when Windows is set to English (United States) local in Control Panel:
• #May 13, 2009#
• #05/13/2009#
• #5-13-09#
• #13 May 2009#
• #13.May.2009#
The pound signs are just like quotation marks around literal text or square brackets surrounding simple identifiers. In fact, pound signs are provided to help Access distinguish between mathematical operations (5/13/2009 means 5 divided by 13 divided by 2009) and dates (#5/13/2009# means May 13, 2009).
Access’s behavior (adding quotation marks, square brackets, and pound signs) can help you verify that you’ve written expressions correctly. If, for instance, you entered Buyer into the Criteria cell in the ContactType column, and Access did not immediately surround it with quotation marks, you’d know there was a problem. Perhaps the ContactType column isn’t really a text data type, or you’ve entered the value into the wrong column. It’s a good idea to let Access do what it does best, and take advantage of the help Access provides.
Expression Builder
Access has an Expression Builder tool to help you build complex expressions. You can use the Expression Builder almost anywhere you can input an expression (such as when creating a calculated field on a form or report). You can activate the builder tool in two ways:
• Press the Build button on the toolbar (the button with the ellipsis on it)
• Click the right mouse button and select Build from the shortcut menu
Figure 13-6 shows the Expression Builder open on the field containing the buyer’s first and last names expression. The Expression Builder lets you choose fields from tables, mathematical and string operators, and even Boolean operators like And and Or. After building an expression, simply click the OK button to place the new expression into the query or other container.
Figure 13-6
The Expression Builder provides a simple interface for creating expressions.
Special identifier operators and expressions
Access has two special identifier operators: the period (.) and the exclamation point (!). You’ll frequently see these operators used in VBA code, a query’s QBE grid, and object’s property box, and other places in Access.
The exclamation point (often referred to as bang) and period (usually referred to as dot) are provided to help Access distinguish between objects, collections of objects, and properties and other attributes. Together, these operators are often referred to as bang-dot notation. We’ll use tables and fields within tables as our first example of bang-dot notation.
Access tables provide many ways to store and present data. You can use fields and their contents, and field objects can be reused repeatedly. A field is often used in numerous forms and reports, using the same reference (the field name) in each instance.
For example, the SaleDate field in the tblSales is used in several different forms. When you want to use the SaleDate field in an expression for a comparison, how do you tell Access exactly which instance of the SaleDate field to use in the expression? It is possible to have several different forms open at the same time in an application. And, of course, there is always the field itself in tblSales. Which of these instances of SaleDate should be used in an expression?
With all this repetition, there must be a way to tell Access which SaleDate field object you want the expression to use. That is the purpose of the dot and exclamation point as operator identifiers. These symbols clarify which field to use.
A Few Words about Controls and Properties
When you create a form or report, you place many different objects on the form—fields bound to text boxes, labels, buttons, check boxes, combo boxes, lines, rectangles, and so on.
As you select and place these objects on a form, each object is assigned a control name. Access supplies a default control name according to predefined rules. For example, the name applied to a bound control (like a text box) defaults to the name of the field underlying the control. The field name appears in the text box on the form. The label for the text box is assigned the control name Text, with a sequence number attached to it (for example, Text11 or Text12). The sequence number is added to make each control name unique.
After all objects are placed on the form, you identify each object on the form (line, button, text box, and so on) by its control name. This control name is how you refer to a specific table field (or field on a form).
In most cases you change the default name that Access assigned to the object. The only requirement for the new control name is that it must be unique within the form or report that contains it.
Every object on the form (including the form itself) has associated properties. These are the individual characteristics of each object; as such, they are accessible by a control name. Properties control the appearance of the object (color, size, sunken, alignment, and so forth). They also affect the structure, specifying format, default value, validation rules, and control name. In addition, properties designate the behavior of a control—for instance, whether the field can grow or shrink and whether you can edit it. Behaviors also affect actions specified for the event properties, such as On Enter and On Push.
The ! (exclamation) operator
The exclamation mark (!) is used in conjunction with several reserved words. One such reserved word is Forms, which is a reference to the collection of open forms in an Access application. You cannot use Forms to refer to a form that is not currently open.
When Forms is followed by ! Access is being told that the next object name is the form object that you want to refer to.
Additional keywords can be found in the next section, titled “Special Keywords and Properties.”
As an example, say that you have a Description field (Description) that is in two different forms—frmProducts and frmSales, both of which are open on the screen at the same time. You want to refer to the Description field in frmProducts. The way to specify this form is by use of the ! and the Forms reserved word:
Forms![frmProducts]
or, more simply:
Forms!frmProducts
Now that the form is specified, further refine the expression to reference the Description field.
Although earlier chapters cover controls and properties, by this point you should have a partial understanding of what properties and controls are (for a refresher, see the preceding sidebar).
Actually, what you are specifying is a control on the form. That control uses the field you need, which is Description. In this case, the control has the same name as the field. Therefore, you refer to this specific control with the following expression:
Forms![frmProducts]![Description]
The second exclamation mark specifies a control on a form—one identified by the reserved word Forms.
Strictly speaking, the exclamation mark separates a collection (collections are nothing more than aggregates of objects) from an item within the collection. In the preceding example, Forms is the collection, and frmProducts is a single item within the Forms collection.
By following the properties of each object, starting with the Forms collection, you can trace the control source object back to a field in the original table.
In summary, the exclamation-point separator is always followed by an object name. This object name may be the name of a form, report, field, or other control.
The dot (.) identifier operator
The dot (.) is an operator used in expressions. Unlike the !, the dot(.)usually identifies a property or other attribute of an object. The following expression refers to the Visible property of the Description control in the previous example:
Forms![frmProducts]![Description].Visible
This expression refers to the value of the Visible property of the Description control on frmProducts. (You’ll recall that the Forms collection refers only to forms that are currently open in the application.)
Normally, the dot (.) identifier is used to obtain a value that corresponds to a property of an object (frmProducts.Visible). It is also used between a table’s name and a field within the table: tblSales.Buyer and to refer to object methods: DoCmd.OpenForm. A method, as you’ll recall from earlier chapters, is an action supported by an object.
A thorough analysis of the two special identifier operators is beyond the scope of this book. Even so, you’ll find that these identifiers enable you to create expressions that refer to any object in your Access application, and the values associated with its properties. Whenever you see bangs and dots you’ll know that they are nothing more than operators that separate objects, properties, and methods.
The word Forms has special meaning to Access. It actually refers to a collection of form objects. Each time you open a form in an Access application it is added to the Forms collection. There are several ways to refer to an individual form (such as “frmSales”) within the Forms collection:
Forms!frmSales.Caption
Forms(“frmSales”).Caption
Forms.Item(“frmSales”).Caption
Forms.Item(0).Caption
This assumes frmSales was the first form opened. Of these different ways (and, there are even more!), most developers choose the first (bang-dot notation: Forms!frmSales.Caption) or second (Forms(“frmSales”).Caption) syntax to refer to a form and its properties.
Special keywords and properties
Access uses many special keywords and properties to reference active objects. Two have already been referenced earlier in this chapter—the property Me used in VBA to reference forms or reports (as in Me!txtLastName) and Forms used to reference the current active form. Although there are many keywords and properties, the following list includes the most common keywords and properties you will use as references in your events and code for forms and reports:
• Forms: The complete collection of open forms in an application—used to specify a specific form. The Syntax is:
Forms!frmContacts
• Form: The current active form—used to access an object on a sub form within a specific form. The syntax is:
Forms!frmMyForm.mySubFormObject.Form!theControlName
• Reports: The complete collection of open reports in a database—used to specify a specific report. The syntax is:
Reports!rptContacts
• Screen: The Screen object is used for the particular form, report, or control that has focus. The Screen object provides many properties to reference objects that currently have the focus. The syntax is:
• Screen.ActiveForm: Used for active form
• Screen.ActiveReport: Used for active report
• Screen.ActiveDatasheet: Used for active datasheet
• Screen.ActiveControl: Used for active control
Be aware that a reference to a Screen property may be invalid if the referenced object (such as ActiveDatasheet) is not open on the screen, and does not have focus.
• Me: Me is a special property that is used to reference the active form, report, or class module. It can only be used in VBA code. The syntax is:
• Me!txtLastName: The same as Forms!frmContacts.txtLastName)
• Me!txtLastName: The same as Screen.ActiveForm.txtLastName)
As you work with your forms, report, and Visual Basic code, these special keywords and properties will be useful for writing efficient events.
A Quick Review of Events and Properties
Simply put, an event is an indication that some action has occurred, or is occurring at this very moment. An event may indicate an action such as opening a form or report, changing data in a record, selecting a button, or closing a form or report. Access recognizes approximately 60 different events in forms, reports, and controls.
Access uses special event properties to hook events to objects. Each event has an associated event property. For example, the OnOpen event property is associated with a form or report’s Open event.
To perform some action when the event is triggered, you create a macro or VBA code and associate it with the property (in the previous case, the OnOpen event property) through the event property on the object’s property sheet. The event procedure runs when the event is raised by the object.
Understanding Functions
Functions are procedures that, by definition, return a value. The value returned can be string, logic, or numeric, depending on the type of function. Access provides hundreds of built-in functions (such as Date()) that can be used in tables, queries, forms, and reports. You can also create your own user-defined functions (UDFs) using the Visual Basic for Applications language.
Using functions in Access
Functions perform specialized operations that enhance the utility of Access. Many times, you find yourself using functions as an integral part of Access. The following are examples of the types of tasks that functions can accomplish:
• Provide a default value for a field in a table
• Place the current date and time in a control on a report
• Convert data from one type to another
• Perform financial operations
• Format data in a table’s field
• Look up and return a value based on some other value
• Perform an action upon the triggering of an event
Access functions perform financial, mathematical, comparative, and other operations. Functions are used just about everywhere in Access applications—in queries, forms, reports, validation rules, and so forth.
Many Access functions evaluate or convert data from one type to another; others perform an action. Some Access functions require the use of parameters; others operate without them.
A parameter is a value that you supply to a function. The value can be an object name, a constant, or a quantity. Not all functions require parameters, and not all parameters are required.
Access functions can be quickly identified because they always end with parentheses. If a function uses parameters, the parameters are placed inside the parentheses immediately after the function name.
Examples of Access functions are as follows:
• Now(): Returns the current date and time
• Rnd(): Returns a random number
• DateAdd(): Returns a date based on an interval added or subtracted from a date
• Ucase(): Returns the uppercase representation of a string
• Format(): Returns a formatted expression
Types of functions
Access offers several types of functions. They can be placed in the following general categories:
• Conversion: Convert one value to another.
• Date/Time: Return date and/or time values.
• Financial: Perform financial operations (such as NPV) on numeric values.
• Mathematical: Perform mathematical operations (SQRT) on numeric values.
• String manipulation: Transform, combine, or otherwise manipulate strings.
Immediate window
Microsoft Visual Basic has an Immediate window that you can use to test your code or functions. Figure 13-7 shows the Immediate window containing two lines of text—the UCase() function and its return value.
Figure 13-7
The Immediate window demonstrating the UCase() function.
The Immediate window is a nice tool for checking on how a function works.
Activate the VBA editor by pressing Ctrl+G or by right-clicking a module in the Navigation Pane and selecting Design View from the shortcut menu. When you are in the VBA editor, select View⇒Immediate Window or press Ctrl+G. When you are active, use the print command (a question mark: ?) to display the results of a function.
Conversion functions
Conversion functions change the data type from one type to another. A few common functions are listed here:
• Str(): Returns a string, converted from a numeric. It always reserves (adds) a leading space for the plus sign:
• Str(921.23) returns “ 921.23”. A leading space is added as a placeholder for a plus or minus sign.
• Str(-123) returns “-123”. No leading space is added and the sign is displayed.
• LCase(): Returns a string that is converted to lowercase:
• LCase(“Woodrow Wilson”) returns “woodrow wilson”.
• UCase(): Returns a string that is converted to uppercase:
• UCase(“Abraham Lincoln”) returns “ABRAHAM LINCOLN”.
• Val(): Returns a numeric value found in a string up to the first non-numeric character in the string:
• Val(“1234.56”) returns 1234.56.
• Val(“10 Farmview 2 Ct”) returns 10. The 2 is after the first non-numeric character, F.
• CDate(): Converts a string to a date:
• CDate(“04 Feb 07”) returns 02/04/2007.
• CDate(“February 4, 2007”) returns 02/04/2007.
• CSTR(): Converts a numeric or Date to a string:
• CSTR(#Feb 04, 07#) is converted to “02/04/2007”.
• CSTR(12345) is converted to “12345”.
• Format(): Returns an expression according to the user-specified format:
• Format(“Next”,”>”) returns NEXT.
• Format(“123456789”,”@@@-@@-@@@@”) returns 113-45-6789.
• Format(#12/25/07#,”d-mmmm-yyyy”) returns 25-December-2007.
• Format(Date(), “Long Date”) returns the current date, such as Wednesday, April 16, 2007.
• Format(Now(), “Long Time”) returns the current time, down to the second: 2:37:58 PM.
The Format function is one of the most powerful ways to display data in a specific format. You provide a format specifier by using keywords or a mask of symbols telling the Format() function how to display the data. Figure 13-8 shows a query using two Format() functions, both using a keyword—Long Date for the date and Percent for the discount percent.
Figure 13-8
The Format() function used in a query to display data in specific formats
Figure 13-9 shows the resulting datasheet using the Format() function for the two fields. Notice that it shows the fields alongside each formatted field.
Figure 13-9
The datasheet from the Format() function used in Figure 13-8.
Date/Time functions
Date/Time functions work with date and time expressions. The following are some common Date/Time functions:
• Now(): Returns the current date and time: 04/16/2007 12:22:34 PM.
• Time(): Returns the current time in 12-hour format: 12:22:34 PM.
• Date(): Returns the current date (versus Now(), which returns date and time): 04/16/2007.
• Month(): Returns a whole number that represents the month portion of a date.
• Month(Now()) returns 04 (or today’s month number).
• Day(): Returns a whole number that represents the day portion of a date.
• Day(Date()) returns 16 (or today’s day number of the month)
• Weekday(): Returns a whole number that represents the day of week for a specific date.
• Weekday(Date()) returns 4 (for Wednesday or today’s day of week number).
• Year(): Returns a whole number that represents the year portion of a date.
• Year(Date()) returns 2007 (or today’s year).
• DateDiff(): Returns a number based on a specific time interval between two different dates. The time interval can be d (day), ww (weeks), m (months), q (quarters), yyyy (years). The syntax is:
• DateDiff(“d”, Date(), #02/04/92#) returns 5550 (the number of days) if the current date is April 16, 2007.
• DateDiff(“yyyy”, Date(), #02/04/92#) returns 15 (the number of years) if
• DateDiff(“q”, Date(), #02/04/92#) returns 61 (the number of quarters) if the current date is April 16, 2007.
• DateAdd(): Returns a new date based on a specific time interval. The time interval can be d (day), ww (weeks), m (months), q (quarters), yyyy (years). The syntax is:
• DateAdd(“d”,22, Date()) returns 5/8/2007 if the current date is April 16, 2007.
• DateAdd(“ww”, 10, #01/01/2007#) returns 3/11/2007.
• DatePart(): Returns a number based on a specific time interval for a date. The time interval can be d (day), y (day of year), w (weekday), ww (weeks), m (months), q (quarters), yyyy (years). The syntax is:
• DatePart(“y”, Date()) returns 106 if the current date is April 16, 2007.
• DatePart(“ww”, Date()) returns 16 if the current date is April 16, 2007.
• DatePart(“q”, Date()) returns 2 if the current date is April 16, 2007.
Notice that all date manipulations are relative to the regional settings specified in Control Panel. The examples here use the English (United States) regional setting.
Financial (SQL) functions
Financial (SQL) functions perform aggregate financial operations on a set of values. The set of values is contained in a field. The field can be in a form, report, or query. Two common SQL functions are listed below:
• Avg(): An example is Avg([Scores]).
• Sum(): An example is Sum([Gross Amount] + [Tax] + [Shipping]).
Financial (monetary) functions
Financial (monetary) functions perform financial operations. Several monetary functions are listed below:
• DDB(): Returns the double-declining balance method of depreciation return. The syntax is:
DDB(InitialCost, SalvageValue, LifeOfProduct, DepreciationPeriod)
where:
• InitialCost is a Double specifying the initial cost of the asset.
• SalvageValue is the value of the asset at the end of its useful life (also a Double).
• LifeOfProduct is an Integer that specifies the length of the useful life of the product (usually months or years).
• DepreciationPeriod is a Double that specifies the period for which the depreciation is calculated.
• NPV(): Returns a Double floating-point value representing the net present value of an investment. The syntax of NPV is:
NPV(DiscountRate, CashFlowArray())
where:
• DiscountRate is the discount rate (expressed as a Double floating-point number) applied over the length of the period.
• CashFlowArray() is an array of Double floating point numbers representing the cash flow values.
• FV(): Returns the future value of an annuity based on periodic, fixed payment and fixed interest rate. The syntax is:
FV(Rate, PaymentPeriods, Payment [, PresentValue] [, Type])
where:
• Rate is a Double specifying the interest rate paid per period. If the interest rate is specified on an annual basis, but payments are made every month, divide the interest rate by 12 to get the interest rate per month.
• PaymentPeriods is an Integer specifying the total number of payments in the annuity.
• Payment is a Double indicating the payment made each period.
• PresentValue (optional) is a Variant indicating the present value (usually expressed as a lump sum) of the series of payments.
• Type (optional) indicates when the payment is made. A 1 indicates the payment is made at the beginning of each period, while 0 (zero) indicates the payment is made at the end of each period.
• PV(): Returns the present value of an annuity based on periodic, fixed payments to be paid in future and fixed interest rate. The syntax is:
PV(Rate, PaymentPeriods, Payment [, FutureValue] [, Type])
where:
• Rate is a Double specifying the interest rate paid per period. If the interest rate is specified on an annual basis, but payments are made every month, divide the interest rate by 12 to get the interest rate per month.
• PaymentPeriods is an Integer specifying the total number of payments in the annuity.
• Payment is a Double indicating the payment made each period.
• FutureValue (optional) is a Variant indicating the future value after making the last payment.
• Type (optional) indicates when the payment is made. 1 indicates the payment is made at the beginning of each period, while 0 (zero) indicates the payment is made at the end of each period.
• SYD(): Returns the sum-of-years depreciation of an asset for a specific period. The syntax is:
SYD(CostOfAsset, SalvageValue, LengthOfUsefulLife, Period)
where
• CostOfAsset is a Double representing the initial cost of the asset.
• SalvageValue is a Double indicating the value of the asset at the end of its useful life.
• LengthOfUsefulLife is a Double specifying the useful life of the asset (usually months).
• Period is a Double indicating the period for which depreciation is calculated.
• PMT(): Returns the payment for an annuity based on periodic, fixed payment and fixed interest rate. The syntax is:
PMT(Rate, NumberOfPayments, PresentValue)
where:
• Rate is a Double specifying the interest rate per period.
• NumberOfPayments is an Integer indicating how many payments are made over the life of the annuity.
• PresentValue is a Double indicating the present value (as a lump sum) that the series of payments is worth.
PMT(.005, 360, -110000) returns payment amount of 659.51 for a 6 percent loan of 360 months for $110,000 USD.
Mathematical functions
Mathematical functions perform specific calculations. The following are some mathematical functions, with examples of how to use them.
• Abs(): Returns the absolute value of a number (the number without a sign):
• Abs(-14) returns 14.
• Abs(14) results in 14.
• Fix(): Determines the correct integer for a negative number:
• Fix(-1234.55) results in -1234.
• Int(): Returns the integer of a specific value:
• Int(1234.55) results in 1234.
• Int(-55.1) results in -56.
• Round(): Returns a number rounded to the specified number of decimals:
• Round(14.245, 2) results in 14.24; rounding occurs over 5.
• Round(17.1351, 2) results in 17.14, rounding up to .14.
• Rnd(): Returns a random number:
• Rnd() (with no argument) returns a random number—the next in the sequence of random numbers.
• Rnd(-1) or any negative number returns the same random number every time, using the number as the seed (-1 in this case).
• Rnd(1) or any positive number returns a random number—the next in the sequence.
• Sgn(): Determines the correct sign of a number:
• Sgn(-14) results in -1 as will any negative number.
• Sgn(12) results in 1 as will any positive number.
• Sgn(0) results in 0.
• Sqr(): Determines the square root of a number:
• Sqr(9) returns 3.
• Sqr(14) returns 3.742.
There is another mathematical operator, MOD (modulus division), which returns the remainder when one number is divided by another. For example:
• 10 MOD 2 results in an answer of 0 (10 is evenly divisible by 2 with no remainder).
• 10 MOD 3 results in an answer of 1 (10 is divisible by 3, 3 times with a remainder of 1).
• 10 MOD 4 results in an answer of 2 (10 is divisible by 4, 2 times with a remainder of 2).
String manipulation functions
String functions manipulate text-based expressions. Here are some common uses of these functions:
• InStr(): Returns a number that represents the first position of one string in another string:
• Instr(“abcd123efg234”, “23”) returns 6, the start position of 23.
• Instr(7, “abcd123efg234”, “23”) returns 11—the 7 in the beginning tells InStr() to start after position 7 of the string.
• Left(): Returns the leftmost characters of a string:
• Left(“abcdefg”,4) returns “abcd”.
• Len(): Returns the length of a string:
• Len(“abcdefgh”) results in 8.
• Lcase(): Returns the lowercase of the string:
• Lcase(“Angus Young”) returns angus young.
• LTrim(): Removes leading spaces from a string:
• LTrim(“ abcd”) returns “abcd”.
• Mid(): Returns characters from the middle of a string:
• Mid(“abcdefgh”,3,4) returns “cdef”, starting at position 3 and reading 4 characters.
• Right(): Returns the rightmost characters of a string:
• Right(“abcdefg”,4) returns “defg”.
• RTrim(): Removes trailing spaces from a string:
• RTrim(“abcd “) returns “abcd”.
• Space(): Inserts the specific number of spaces:
• Space(6) returns six blank spaces.
• Trim(): Removes leading and trailing spaces from a string:
• Trim(“ abcd “) returns “abcd”.
Programming functions
Programming functions are those that don’t fit in a specific category, yet are very useful in programming. The following are some programming functions, with examples of how to use them.
• Choose(): Returns a value based on an index parameter from a list.
• Choose(2, “Slow”, “Average”, “Fast”) returns “Average”.
• Choose(3, “A”, “B”, “C”, “D”) returns “C”.
• IsDate(): Determines whether an expression is a valid date.
• IsDate(“Feb 29, 2000”) returns TRUE because the year 2000 was a leap year.
• IsDate(“Jup 4, 2007”) returns FALSE.
• IIF(): Immediate IF (IIF) is used to return either of two parts, based on an evaluation within the function:
Function TestIt(TestNum as Integer)
‘If TestNum is larger than 250, returns the word “Greater”
‘If TestNum is less than 250, returns the word “Smaller”
TestIt = IIF(TestNum > 250, “Greater”, “Smaller”)
End Function
• IsMissing(): Determines whether a parameter has been passed to a function:
Dim ReturnVal As Integer
ReturnVal = ReturnCheck()
ReturnVal = ReturnCheck(4)
Function ReturnCheck(Optional ABC As Integer)
If IsMissing(ABC) Then
ReturnCheck = NULL
Else
ReturnCheck = ABC * 2
End If
End Function
IsMissing is only valid when a parameter has been declared with the Optional keyword.
• IsNull(): Determines whether an expression has no value (no data—Null), returning True or False:
• IsNull([LastName]) returns False if there is a value in the field or True if no value is present.
• NZ(): Use this function to return a zero, a zero-length string, or another value when a variant is null. The default is a zero-length string.
• xName = “Mark”
• ? Nz(xName) results in “Mark”.
• ? Nz(yName) results in “”.
• ? Nz(yName, 0) results in 0.
Domain functions
A domain is a set of records contained in a table, a query, or an SQL expression. A query’s recordset is an example of a domain. Domain aggregate functions return statistics about a domain. Domain aggregate functions are often used to perform statistical calculations in VBA code. Domain aggregate functions are also used to specify criteria, update values, or even create calculated fields in a query expression.
Several examples of domain functions are listed here:
• DAvg(): Returns the arithmetic mean (average) of a set of values.
• DAvg(“Cost”,”tblProducts”) determines the average cost of vehicles sold. Figure 13-10 shows an example (qryDAvg) using DAvg() to show only vehicles where the cost is greater than or equal to the average of all cars in tblProducts.
• DCount(): Returns the number of records specified.
• DCount(“ProductID”,”tblProducts”, “Category = ‘cars’”) counts all records in tblProducts whose Category value is ‘cars’. The answer should be 25 for the table.
• DFirst(): Returns a random record from a field in a table or query, when you need any value.
• DFirst(“LastName”, “tblContacts”) returns a random name from the LastName field. DLast() works the same.
• DLookup(): Returns the value of a specific field from the specified records.
• DLookUp(“[Short Name]”, “[tblPayType]”, “[tblPayType].[PaymentType] = ‘“&[tblSales].[PaymentMethod]&”’”) finds the short name for all payment types in the query. Figure 13-11 (qryDLookup) shows how the query’s field will look.
• DMax(): Returns the highest value in a range of values.
• DMax(“Cost”,”tblProducts”) returns the highest cost ($165,000.00 USD) in tblProducts.
• DMin(): Returns the lowest value in a range of values.
• DMin(“Cost”,”tblProducts”) returns the lowest price cost ($200.00 USD) in tblProducts.
• DSum(): Returns the sum for a set of records specified.
• DSum(“Cost”,”tblProducts”, “Category = ‘cars’”) sums the Cost field in all records where the Category is ‘cars’. The answer is $779,356.00 USD.
Figure 13-10
Here the DAvg() function is used to show only records that are valued greater than or equal to the average of all vehicles.
Figure 13-11
The DLookUp() function is being used in a query to show values found in a table not included in the query.
Although VBA code and fancy queries can produce the same results as the domain aggregate functions, using these functions is much easier and much faster than the equivalent code or queries.
Using the DLookUp() function for lookup tables
The DLookUp() function is difficult for people to understand. It is a way to find a specific field value by looking up information based on a condition. As its name implies, DLookUp() looks up information in a table (the domain) that is not currently open.
Although it can be easy to program and works well with small amounts of records, if your tables contain more than 5,000 records, you should probably perform the lookup with VBA code.
DLookUp has the following general syntax:
DLookUp(“[Field]”, “[Table]”, “<Criteria>”)
“[Field]” is the field in the table you want to search. “[Table]” is the table containing the field you want to display. “<Criteria>” specifies criteria used by the lookup function. The Criteria parameter is optional, but if you want to use different criteria for each record, it is essential.
The following simple example returns the address (“108 Thomas Road”) from tblContacts where the ContactID is 27:
DLookup(“Address”, “tblContacts”, “ContactID = 27”)
When using DLookUp(), the format of your criteria is critical. Specifying the criteria is no problem when the lookup value is numeric, as in the preceding example. Formulating the criteria becomes much more difficult when working with string values.
The Criteria parameter has the following general syntax:
“[Field in Table] = ‘<Example>’ “
You can replace the equal operator (=) with any Access operator such as > or <>.
‘<Example Data>’ in single quotes is usually a literal, such as ‘Cars’ or ‘AMEX’. An example is:
DLookup(“Address”, “tblContacts”, _
“Company = ‘Circle Auto Sales’”)
In this example, because the name of the company (Circle Auto Sales) is a string, it must be surrounded by single-quotes.
DLookup becomes more complex when the Criteria value references a text field in a table. In this case, you must reference the table and the field, and let Access extract values from the field during the lookup. An example of a field reference is:
tblContacts.Company
You have to keep in mind that the field contains a string, so it must be surrounded by quote marks:
“ & tblContacts.Company & “
The ampersands surround the value in the field with quotation marks.
Here comes the really confusing part. You’ll recall that the Criteria parameter is a string:
“Company = ‘Some String Value’”
In this case, the Criteria is a string that contains another string (the value in the field). Because you are using a string contained within a field in another table, you must be sure that the field’s value is surrounded by the single quote marks. You saw such an example in Figure 13-11. This query was created by following these steps:
1. Select tblSales in the Query Design window.
2. Double-click the SaleDate field in the table, and any other fields you want to look at.
3. In an empty field in the QBE pane, type the following:
How Pay: DLookUp(“ShortName”, “tblPayType”, _
“tblPayType.PaymentType = ‘“ _
& tblSales.PaymentMethod & “‘“).
“ShortName” is the name of a field in tblPayType.
tblPayType is the domain for the DLookup function. This means you will extract data from tblPayType where the PaymentType field matches the PaymentMethod value in tblSales. In other words, you want to see the ShortName (such as “CHCK”) from tblPayType where the PaymentType field value is the same as the PaymentMethod field in tblSales. The Criteria parameter for this example is:
“tblPayType.PaymentType = ‘“ & tblSales.PaymentMethod & “‘“
Notice that the tblSales.PaymentMethod field reference is surrounded by single quotes that are added through concatenation operators (the ampersands). On the left side, the single quote is enclosed within the double quotes containing the first part of the parameter, and the single quote to the right of the field reference is enclosed in two double quotes. The field reference itself is not enclosed in quotes (“tblSales.PaymentMethod”) because Access would interpret this reference as a string literal.
The results for qryDLookup are shown in Figure 13-12. Notice that several records have no Payment method name, because these records have not yet been paid.
Figure 13-12
The datasheet using the DLookUp() function is used here in a query to show values found in another table.
Summary
Expressions are powerful tools you use in many places in Access applications. You use expressions as validation rules, to create fields within queries, as control properties, and embedded within your VBA code.
Access provides many functions that can be combined with identifiers, operators, and other information to build complex expressions. Very often a relatively simple expression replaces numerous lines of VBA code or substitutes for a fairly complex query.
Chapter 14 explains the heart of VBA programming in Access applications. Although many Access database applications are built entirely of bound forms, VBA procedures provide incredibly flexibility when dealing with data. As you’ll see in Chapter 14, there is no substitute for manipulating individual records and fields using the ADO (ActiveX Data Objects) and DAO (Data Access Objects) syntaxes.