Creating Expressions in Monarch Classic

Monarch Classic expressions are used to define filters and calculated fields and to perform field-based searches in the Table window. An expression is a formula that returns a value. Expressions can be up to 32768 characters in length and can include field names from the table, constants, arithmetical, logical, comparison and concatenation operators, and functions.

The following rules apply to all expressions.

Using field names

A field name may be used anywhere in an expression where the field’s values would be appropriate, such as City="Boston", which compares the values found in the City field to the string "Boston".

Note: When typing a field name into an expression, you must enclose the field name in square brackets ([]) if the field name does not conform to the dBASE III field naming rules. These rules state that a field name may not be longer than 10 characters, may not include spaces or punctuation characters, and must begin with a letter (a – z, A – Z). If you prefer not to remember these rules when entering field names in expressions, you may simply select the field name from the Fields list box and click the Insert button (or double-click the field name in the fields list). When inserted in this manner, Monarch Classic determines whether the square brackets are required and adds them if necessary. Monarch Classic will also update your expressions automatically if you subsequently change a field name that has been included in an expression.

Using constants

A constant is a number or string that does not change. The following syntax rules apply to the use of constants:

Character strings must be enclosed in double quotes, as in "Boston".
Date values must be enclosed in braces as in {June 17, 2001} or {03/28/98}. Dates must fall in the range between January 01, 1900 and December 31, 2400.
Numbers must contain no more than 15 significant digits, excluding the optional negation sign and decimal point, as in -1234567890.12345. Note: Numbers are entered without quotes or braces.

Using mathematical operators

An arithmetical operator is used to perform calculations on numeric values. Monarch Classic expressions support the following arithmetical operators:

String functions operate on or with character strings.

Operator	Description
+	addition
-	subtraction
*	multiplication
/	division

Exponents are indicated with either ^ or **. The expression MASS^3 (or MASS**3) returns the cube of the value found in the MASS field.

Using comparison operators

A comparison operator is used to compare two values. Monarch Classic expressions support the following comparison operators:

Operator	Description
=	equal to
<>	not equal to
>	greater than
>=	greater than or equal to
<	less than
<=	less than or equal to

Note that asterisk (*) and question mark (?) characters are interpreted as wildcards when comparing strings during expression evaluation"

For example:

"Donna *" = "Donna Smith"

"Do??a" = "Donna" and "Do??a" = "Dotta"

If it is necessary to look for a literal asterisk in a string, you can do the following::

Use the Asc() function to directly compare character code values.
For example:

If (Asc(DateLeft)=Asc("*"),"Arrival", if (DateLeft=" ","Close Out","Departure"))
Use the Instr() function to search for a literal asterisk in the target string, as follows:
For example:

if (Instr("*",DateLeft) > 0,"Arrival", if (DateLeft=" ","Close Out","Departure"))
Create a column called "Asterisk" containing a single asterisk character. Then refer to this column in your expression, as follows:
if (DateLeft=Asterisk,"Arrival", if (DateLeft=" ","Close Out","Departure"))

Note: See Using Comparison Operators in Expressions for more information.

Using string operators

The string operators (+ and -) may be used to concatenate (i.e., to combine) character strings. The plus sign (+) concatenates strings. The minus sign (-) first removes any trailing spaces from the first string, then concatenates the strings. The plus sign (+) is used more often since it produces a more intuitive expression. For example, to create a Fullname field from a Firstname field and a Lastname field, use the expression:

Firstname+" "+Lastname

Firstname+" " concatenates one space to separate the first name from the last name, then +Lastname appends the Lastname field.

In the expression Firstname-" "+Lastname the minus sign is used to right trim the Firstname field before concatenating the Lastname field.

Using logical operators

Logical operators are used to combine comparisons. Each of the logical operators is described below:

Operator	Description
.And.	Requires both halves of an expression to be TRUE.
.Or.	Requires either half of an expression to be TRUE.
.Not.	Requires an expression to be FALSE.
.In.()	Determines whether a value is present within a list of values.
.NotIn.()	Determines whether a value is not present within a list of values.

Note: See Using Logical Operators in Expressions for more information.

Handling invalid values

When a filter expression references a field that contains one or more invalid values, the records that contain the invalid values are ignored (i.e., will not be returned by the filter). A field value is considered invalid if it is a calculated field value for which division by zero occurs or it is a date field value that contains a blank (an invalid date).

Inserting comments

Monarch Classic allows comments in expressions. Comments must be inserted between the comments brackets /* */. You may place comments anywhere in an expression as long as they do not interrupt a field name or value. For example, the following expressions are valid:

Unit_price*Quantity/*Calculates transaction totals*/
Unit_price*/*Calculates transaction totals*/Quantity

However, the following expression is not valid because the comment interrupts the Quantity field name:

Unit_price*Quan/*Calculates transaction totals*/tity

Nested comparisons

Monarch Classic allows any combination of logical operators in an expression. However, you must use parentheses where appropriate to achieve the desired result. In Monarch Classic, you are allowed unlimited nested pairs of parentheses.

Using wildcards

A wildcard may be used to represent any character or any series of characters in a text string. Monarch Classic expressions support two wildcard characters, the question mark (?) and asterisk (*). Use a question mark to indicate "any single character". Use an asterisk to indicate "any series of characters that follow".

Case sensitivity

Comparisons performed within expressions are not case-sensitive. For example, the expression

If(Customer="Ace Resellers",Unit_price*.55, Unit_price*.65)

computes a discount rate for a preferred customer (Ace Resellers) and a higher rate for all other customers. This expression will compute the discount rate for "Ace Resellers" when the Customer column contains any of the following values:

ace resellers
Ace Resellers
ACE RESELLERS

Using functions

Monarch Classic expressions support a variety of functions. Most functions require one or more arguments and all return either a character string, a number, or a date. Monarch Classic provides help below the expression builder by showing you the syntax of a function you have selected.

The following syntax rules apply to all functions:

A function name may be entered in uppercase or lowercase letters.
Function arguments must be enclosed in parentheses. If multiple arguments are required, they must be separated by commas and must appear in the order specified by the function definition.
A function may be embedded as an argument within another function.

Note: For a detailed description of each supported function, along with examples of each function's use in calculated field, filter and find expressions, see Functions Quick Reference.

Using Aggregations

An aggregation is a command that performs an arithmetic function on the values of a measure in a summary. The Formula Tab for Measures on the Summary Edit Fields window lists aggregations that can be used in measure expressions. See Using aggregations for more information.

See Creating Compound Expressions for related information.