The Expression Builder is a dialog that helps you enter expressions without having to know real table and field names. This dialog may be invoked by clicking the button with the ellipsis (...) beside a text box where an expression is normally typed. In addition to making it easier to enter expressions, the text box you type in is much larger in the Expression Builder and can be made even larger by resizing the dialog. To resize the Expression Builder window, click the lower right corner of the window and drag until the window is the desired size. Stonefield Query remembers the size and position of the Expression Builder and restores it the next time you run it.

The Expression Builder is available from the following locations in Stonefield Query:

• In the Filter Condition dialog for the custom description and for the values when Compare to is set to Expression.

• In the Customize SQL Statement and script steps of the Customize Report Wizard.

• In step 5 of the report wizards for the header and footer of a report.

• In the Output page of the Reports Explorer for the subject and message of an email.

• In the various properties dialogs in the Advanced Report Designer, including the Print When expression, the Expression for a field or data group, the Caption for a label, the Initial Value and Value to Store for variables, and the On Entry and On Exit expressions for bands.

The Expression Builder has the following options:

• Expression: the expression you wish to use. You can type any valid expression directly in the Expression text box or add fields or functions to it from the other controls in the dialog. Note that pressing Enter to add a line break is allowed in the header and footer of a report, the message of an email, and in the Customize Report Wizard; it's ignored in all other places since line breaks aren't allowed in expressions.

• Table: a drop-down list that displays the names of the tables you can select fields from.

• Available fields from table: a list containing all reportable fields from the table shown in the Table list. To add a field to the Expression text box, double-click the desired field or select it and press Enter. The field is added at the current cursor position in the Expression text box, surrounded with curly braces (for example, ) to indicate that this is a field.

• Commonly used expressions: a drop-down list of commonly used expressions. For example, the expression "date() - day(date()) + 1" evaluates to the first day of the current month, but this expression is difficult to remember and type properly. So, simply choose the expression from the list and click the Insert button to add the expression to the Expression text box. Note that some functions are for date fields and others are for date/time fields.

• Built-in functions: a list of the functions built into Stonefield Query. When you select a function in the list, the box below the list displays a description of the function and the parameters to pass to it. Select the desired function from the list and then click the Insert button to add it to the Expression text box. See the Function Reference help topic for additional information on the most commonly used functions.

• Test Syntax: click this button to test whether the expression is valid. Note this only tests for the syntax of the expression, not whether it is completely valid. For example, an expression like "LEFT(SomeNumericField, 10)" is syntactically correct but isn't valid because the LEFT() function is for character fields, not numeric fields.

• Values: click this button to display a list of values for the field selected on the Available list. If you select a value and click OK, that value is inserted into the expression.

Once you have entered the desired function, click the OK button to save it or Cancel to cancel and close the dialog.