ACCESS: FORMULAS

In Access, formulas are commonly referred to as expressions. This can get tricky at times because an expression can be either the whole or just a part. To simplify matters, we’ll use “formula” to represent the whole and reserve “expression” for referring to a part or element of the whole.

Formulas in Access can contain functions, operators, references (identifiers), and/or constants. They involve any number of expressions to calculate values, validate data, or even set a default value for a field or control.

Depending on how it is used, a formula may need to be prefaced with equal sign (=), just as an Excel formula. Figure 1 shows examples of formulas that return numeric or date results. Figure 2 shows formulas that result in a string (text).

FUNCTIONS

Functions return a value. There are many built-in functions, and you can also write custom functions in Access.

Many of the functions follow the same kind of syntax as in Excel. In other words, the function name is followed by possible arguments or parameters in parentheses. For example, the Format function needs arguments to specify what to convert and the format code with instructions: =Format([EOMSale],“yyyy”“Quarter”“q”). Yet not all functions in Access require arguments. The Now function, for example, doesn’t need arguments to return the current date and time. It simply reads the system clock.

There are many different categories of built-in functions. Some of the basics include conversion, date/time, financial, math, and text.

Conversion functions are used to change data type, which is important since Access is always concerned about data type. Examples include Nz (null-to-zero), CCur (convert to currency), and CDate (convert to date).

Date/time functions involve date and time. For example, DateAdd lets you get a new date by adding or subtracting an interval of time to a specified date. The Year, Month, and Day functions extract their respective parts from a date. Hour, Minute, and Second do the same with time. And the Date function returns today’s date.

Financial functions involve currency: PV (present value), FV (future value), IRR (internal rate of return), Pmt (payment for an annuity), and SLN (straight-line depreciation of an asset for a single period).

Math functions include Abs (absolute value), Sin (sine of an angle), Round (round a number to specified number of decimal places), Log (logarithm), and Int (round down to nearest integer).

Text functions include Left, Right, Mid, Len, and Trim. Many of the text functions in Excel exist in some form in Access. They just don’t always have the same name: TEXT in Excel is Format in Access, UPPER is UCase, LOWER is LCase, and FIND is similar to InStr.

OPERATORS

Operators are used between different parts of a formula. Arithmetic operators are addition (+), subtraction (-), multiplication (*), division (/), divide and truncate to integer (), divide and return remainder (Mod), and raise to power of an exponent (^).

Comparison operators are used between two expressions to create a formula that returns True or False. The expressions can be compared using equal (=), not equal (<>), less than (<), less than or equal to (<=), greater than (>), greater than or equal to (>=), or where an expressions is in a range (Between value1 And value2), in a set (IN (list)), or matches a rule (Like “pattern”).

Logical operators combine two expressions and evaluate to True or False, such as AND (both expressions true), OR (either expression true), EQV (both true or both false), and XOR (true if one is true but not both). NOT reverses True to False and False to True.

To produce a single piece of text from more than one string (and/or values that can be converted to string), use the ampersand (&) to concatenate. To conditionally combine strings only if all results have a value, use the plus sign (+) to join. For instance, the last example in Figure 2 shows how a space after a middle name is added only if there also is a middle name.

Parentheses can be used to group operations in a formula or expression, and they influence the order of calculation, which follows the standard mathematical rule and looks left-to-right for: Parentheses, Exponents, Multiplication and Division, then Addition and Subtraction (or, as you might remember from math class, PEMDAS).

REFERENCES

A reference represents a value in the database and generally specifies a field, like [DateSale], or a control, like Forms!f_ReportMenu!StartDate. References may include periods (.) and/or exclamation points (!). If you enter a reference that Access doesn’t understand or can’t evaluate, it will prompt you for a parameter when it tries to calculate that formula—a dialog window will appear with a message asking for the parameter. If you misspell a reference, Access assumes you want to be prompted. While a flexible feature, if the prompt text looks like Forms!Formname!Controlname instead of [Enter Start Date:], it may be a bit confusing to the user.

CONSTANTS

Constants keep the same value each time the formula is evaluated. Figures 1 and 2 show constant values in red. Note that the text constants are surrounded with quotation marks. Similarly, date values are denoted by beginning and ending with #. These markers are called delimiters. Number constants aren’t delimited.

Next month we will dive in to a helper that Access has to construct formulas called the Expression Builder.

SF SAYS

When you want to calculate data instead of showing the actual values, use a formula.