The format for a comparison operator validation check is a string containing one or more comparison operators. Comparison operator validation checks have the following conventional syntax:

The fieldname is the internal name of the field being validated; otherfieldname is the internal name of some other field containing a value to be compared against. In a validation string for a table‑field column, you use tablefieldname instead of fieldname, as follows:

The following table lists valid comparison operators for SQL.

You can enter character strings as validation checks for fields designated as character data types. Always enclose character string validation checks in double quotation marks, except for the LIKE operator, which accepts strings with single quotation marks.

Validation checks on character strings can include pattern‑matching characters. The following pattern‑matching characters are valid for all operators except the LIKE operator:

Example character string validations:

Enter comparison constants for abstract data types such as date and money in double quotation marks, as if they were character strings. For example:

The money data type is not supported by OpenSQL. For SQL, OpenSQL and QUEL equivalents, see the appendix "Data Types."

To compare for the null value, use IS NULL. To compare for any value but the null value, use IS NOT NULL. For example:

The following rules govern null values in an expression:

If you use the logical NOT operator with an expression that evaluates to a null value, the result is still null.

Numeric constants do not have to be enclosed in quotes. The following example requires that the number entered in the Salary field be larger than zero:

This example requires that the number entered in the Age field be larger than or equal to 18 and less than or equal to 70:

A complex numeric expression must be enclosed in parentheses to clarify the way the expression must be evaluated.

The following example requires that the number entered in the Hourly_rate field either equal 30 or fall within the range of 40 to 50:

You can compare a value against whatever value is currently contained in another field. The following example requires that the name entered in the Lastname field not be the same as the name entered in the Firstname field:

This example requires that the date entered in the Duedate field be equal to or greater than the date entered in the Orderdate field:

By using the keyword in, you can compare a value against an arbitrary list of values using the following format:

The fieldname parameter is the internal name of the field and list is the list of valid values. The values in list must be of the same data type as values admissible in the field. They must be separated by commas and enclosed in brackets ([ ]).

If the field has a character data type, you must enclose the character string values in double quotation marks and you can use valid pattern‑matching characters. Leading blanks are not significant.

The following example requires that the name entered in the Lname field be either Jones, or Ortega, or Bridges:

This example requires that the character string entered in the Address field contain the characters Bl, or Av, or end in R plus one other letter, or end with St:

This example requires that the amount entered in the Salary column of the Emptable table field be either 1000, 1100, 1200, 1300, 1400, or 1500:

The keyword in can also be used to compare data entered in a field to a set of stored values in a database table. This is the easiest way to compare against a large number of values or against values that can be changed from time to time.

When your form is initialized in QBF or your own application, the current set of data from the lookup table is read into main memory. The column entries in the lookup table at the time of form initialization represent the data values used for comparison. Values added to, or deleted from, the lookup table column after the form is initialized are not reflected in the set of data used for comparison.

To compare against values in a lookup table, use the following format:

If the schema, tablename, or columnname after the in keyword is a delimited identifier, you must enclose it within double quotes:

where:

The following example requires that the value in the Zipprefix field match the values found in the Prefix column of the Zip table:

This example requires that the names entered in the Manager column of the Emptable tablefield match names contained in the Name column of the Employee table:

The user can place the cursor in a field that is validated by a lookup table or by a fieldname in list validation check, as described in Comparison to a List of Values. In either case, choosing the Help Field operation at this point displays the acceptable values from the lookup table.

This chapter has discussed the following validation comparison types:

You can create more complex validation checks by using Boolean operators to connect any of these validating comparison types. The syntax for using the Boolean operators is:

The parameter expression is any of the previously discussed validation comparison types. For example, the following validation check forces a user to enter a number into the Code field that is either less than 21 or is 25, 30, or 35:

To allow additional flexibility, you can use parentheses to group Boolean expressions to achieve the desired semantics in the validation check. For instance, the following example forces a user to enter a number into the code field that is less than or equal to 20000 into the Salary field unless the Grade field is greater than or equal to 7. In this case, the user can enter a number up to 30000:

Negation (NOT) has precedence over conjunction (AND) and disjunction (OR); AND and OR have equal precedence.

Some example character strings with Boolean operators are: