More About Outlook Formulas

In last week's UPDATE, I introduced creating formulas for an Outlook folder view. This week, we'll look at how Outlook forms use formulas, starting with validating data.

For a validation formula on an Outlook form, construct an expression that evaluates to True if the data the user enters for the field is valid, and False if the entry is unacceptable. The expression will always compare that field with another value.

The simplest example occurs when you require the user to enter a value in the field. On the Properties dialog for a control on a form, on the Validation tab, you'll see a checkbox for "A value is required for this field." If you use that setting, however, you can't enter a custom validation message in the "Display this message if validation fails" box. To use a custom validation message, you must also use a validation formula. For require the user to enter data in a text field named MyTextField, you can use this validation formula:

[MyTextField] <> ""

This expression is True if MyTextField contains text, but False if it is blank.

For a date/time field named MyDateField, use:

[MyDateField] <> #1/1/4501#

This works because Outlook stores a date that appears as "None" in the user interface as the actual date 1/1/4501.

What if you want to require an entry in a field, but only if a second field has a particular value? For example, on a custom contact form, you might want to require the user to enter the department name of each contact at the Acme Widget Company—but not for any other company. The formula looks like this:

([Department] <> "") OR ([Company] <> "Acme Widget")

When a formula contains two or more expression joined by "OR," Outlook evaluates each expression and returns True if any expression is true. Therefore, in this example, the formula returns True if the company entered on the contact is not Acme Widget *or* if the Department property has some value. If this is an Acme Widget contact, but you forgot to add the department, the formula returns False, and you'll see the validation message.

Because the Department property appears on the contact form's Details page, which you can't customize, how do you enter a validation formula for it? Switch to an unused blank page, such as the P.2 page, and drag the Department field from the Field Chooser to the form. You can then bring up the text-box properties for that page's Department field. Any validation settings you create there will apply to the Department field on the Details page. You can use the Form, Display This Page toggle command to hide the P.2 page if you used it only to create a control used to modify the validation settings.

A note: If you delete the text box on the P.2 page for the Department field, the validation settings aren't removed. Before removing a control from an Outlook form, it's always a good idea to check the Validation tab first and remove any validation settings.

Next week, we'll conclude with more uses for formulas on Outlook forms and share some formulas sent in by UPDATE readers.