Alpha Five Expression Writing
 


Avoiding Error Messages:

When writing an expression, use the expression builder (click the X-Y icon) and choose field names and functions from the lists instead of typing them in yourself. This will avoid misspellings that are particularly difficult to catch in long expressions.

Match the result of the expression to the field you are using it with. For example, if you make a date field a calculated field in field rules, you must be certain your expression will yield date type data. If necessary, use a converting function such as CTOD (character type to date type).

When using constants in an expression, follow this guide:
Enclose character string constants in quotations. Example: DEPT="Accounting"
Enclose date constants in brackets. Example: DUEDATE>={03/31/97}
Enclose logical constants in periods. Example: PMTRECD=.T.
Don't enclose numeric constants in anything. Example: AMOUNT=132.56


Troubleshooting methods:

If you feel mired in a long and complex expression, consider making a small test database containing various field types. Try to break the expression into smaller problems, testing each piece to see if it works independently. Then begin to rebuild the final expression, piece by piece, checking your results as you go.

Respect the syntax of each function you use exactly. If you forget a comma, parenthesis or quotation mark, Alpha Five will give you an error message. Check for a closing (right) parenthesis for every open (left) parenthesis and an even number of quotation marks. When combining functions to write a complex expression, check for proper placement and nesting of parentheses.

Filters:

Filters must yield a logical result, true or false. There must be an operator such as = , <, <=, >, >=, <> (not equals) or $ (contains character string) in the expression.

Connect logical expressions with .AND. and .OR. using parentheses to clarify the relationship between groups of logical expressions. Example: (STATE="FL".OR.STATE="GA").AND.AMOUNT>=100

IF statements:

The IF syntax must contain both a true and a false result. If you do not wish the false result to effect a change, simply fill in the same field name. For example, when globally updating STATE, the expression IF(UT(CITY)="MIAMI","FL",STATE) will not change the contents of the state field unless the city is Miami.

Save time next time:

Use the Windows clipboard to copy and paste expressions. To copy to the clipboard, highlight the expression (or part of an expression) and press [Ctrl-C]. To paste from the clipboard, position the cursor where you want to insert the expression and press [Ctrl-V].




Return to database tips page

Proctor & Peake, Inc.
9620 NE 2nd Avenue, Suite 209
Miami Shores, FL 33138
305-751-1181 / 1-800-943-6031
e-mail us


top