To add a new condition to the filter, click the Add button. You can also press the Insert key or right-click and choose Add Condition from the shortcut menu. If you don't have any conditions in your filter yet, the Filter Condition dialog shown below appears.

If you already have at least one condition in your filter, the Filter Condition dialog appears a little differently.

The Connection drop-down list, which does not appear if this is the first condition for your filter, is used to define how this condition is connected to the previous condition in your filter. The default connection is and, but you can also choose or or one of various combinations of and or or with parentheses to allow you to group your conditions and specify the order in which the conditions are evaluated. Stonefield Query automatically balances parentheses, so there is no need to worry about a starting or ending parenthesis. See the Creating a Filter topic for a discussion of how connections work.

The Table drop-down list shows the tables you can select fields from. The Field drop-down list shows the fields you can filter on from the selected table. The Show all fields option determines which tables and fields you can select. Initially, this option is turned on, so you can see all tables and fields. Turn this option off if you only want to see tables and fields that actually appear in the report.

After selecting a field, select a comparison operator from the Operator drop-down list. The options that appear in this list depend on the type of field selected. For example, for logical or Boolean fields, the only choices that appear are is Yes and is No. See the Creating a Filter topic for a discussion of how operators work.

Once you've selected an operator, you may enter a value to compare to the field. You won't be allowed to enter a value if you choose the is blank, is not blank, is known, is unknown, is Yes, or is No operators. If you choose the is between or is not between operators, you must enter two values. If you choose the is one of or is not one of operators, you can enter up to ten different values.

If you're not sure what values appear in the field or want to select a value without having to type it, click the Values button. A list of unique values from the field appears in the Select Values dialog. You can choose a single value or multiple values; to select more than one value, hold down the Ctrl key as you click the desired values. If you choose more than one, the operator is automatically changed to is one of. To search the list of values, type something in Search; as you type, the list jumps to the first entry containing what you typed. Press F3 to jump to the next entry. Search is only available for character values.

If you select a date field, you can either type the desired date or click the down arrow at the right of the text box to display a calendar. Whether the calendar is displayed or not, you can press one of the following keys as shortcuts:

Key	Purpose
Ctrl-Home	Today's date
Home	First day of the month
End	Last day of the month
PgUp	Previous month
PgDn	Next month
Ctrl-PgUp	Previous year
Ctrl-PgDn	Next year
Space or F4	Toggle the calendar (displayed or not)
Esc	Close the calendar if it's open
Up Arrow	Next month, day, or year, depending on which is selected (when the calendar is open, the previous week)
Down Arrow	Previous month, day, or year, depending on which is selected (when the calendar is open, the previous week)
Left Arrow	When the calendar is open, the previous day
Right Arrow	When the calendar is open, the next day

When the calendar is displayed, you can select a date by simply clicking it. Click the left or right arrows at the top of the calendar to move to the previous or next month. Click the month/year to display a list of months you can select from. In the month view, click the year to display a list of years to choose from. In the year view, click the decade at the top to display a list of decades to select from. To select today's date, click the word "Today."

Date fields have a special operator other types of fields don't have: is. This operator allows you to choose a descriptive name for a date range. For example, if you choose "is" for the operator and "This Month" for the value, the filter condition includes records where the field is between the first and last days of the current month.

If you select a field with a pre-defined list of values, a drop-down list of those values appears.

If you select a character or memo field, a Case sensitive setting may appear. If you turn this setting on, only those records with the value in the same case (that is, upper and lower case characters) as you typed it are included. If it isn't turned on, case doesn't matter; for example, entering "Jones" matches "Jones," "JONES," "JoNeS," or any other combination of case.

To compare a field to another field rather than a value, select "Field" from the Compare to option to use a field (the default is Value, which means a value is used). Select the field to compare against in the drop-down list that appears. Note that this only allows you to select fields that have the same data type and are from the same table as the field you're filtering on or are in the report. To compare a field in one table to a field in another table that isn't in the report, use an expression (described next) and specify the name of the field in the other table as the expression.

To compare a field to an expression, select "Expression" from the Compare to option. Enter the expression into the text box. For example, to look for all records with a date field falling in the last week, use the "is between" operator and DATE() - 7 for the first expression and DATE() for the second expression. Since DATE() gives today's date, DATE() - 7 is seven days ago, so this gives all records with the date being between one week ago and today. The Function Reference topic has a list of the most useful functions. You can also click the Expression Builder button (the button labeled "..." beside the text box) to display the Expression Builder, which is a much easier way to create the expression to use.

If you want to be prompted to enter the values when you run the report, turn on the Ask at runtime option. If Compare to is set to "Expression," the value of the expression is used as the default for the ask-at-runtime value. Otherwise, the value saved with the condition is used as the default. The setting of the Condition cannot be ignored option determines whether the ask-at-runtime dialog that appears when the run is run has an Ignore this condition option or not. Leaving Condition cannot be ignored turned off means Ignore this condition appears and the user can ignore the filter condition for that particular run by turning it on. Turning Condition cannot be ignored on means Ignore this condition does not appear so the user cannot ignore the filter condition.

By default, all filter conditions are displayed in the header of a report if the Include filter in report header option in Step 5 of the report wizards is turned on. However, if you don't want a particular condition to be included, turn off the Include in filter display setting. This is particularly useful for reports that have a lot of filter conditions, which can get quite long when listed in the report header. You might in that case turn this option off for all but ask-at-runtime conditions.

A filter condition is displayed in a report as "Field operator value", such as "Status equals Active." If you want something more descriptive, enter a custom description for the condition, such as "Active Customers;" anything you put in Custom description is displayed instead. If you want to use an expression rather than fixed text, such as a user-defined function, add "{" and "}" (without the quotes) around field or function names in the text. Specify {Value} as a placeholder for the filter condition value or, for the "is between," "is not between," "is one of," or "is not one of" operators, {Value1} for the first value, {Value2} for the second, and so on. Click the Expression Builder button to display the Expression Builder to make it easier to enter field or function names.

Here's an example of an expression that displays "Sales from date to date" (such as "Sales from 01/01/2018 to 12/31/2018") for the filter condition:

Sales from {Value1} to {Value2}

After you've completed entering the condition, press the OK button to save the condition and add it to the list. Choose Cancel to cancel the new condition.

If you don't enter a value to compare to the field, the operator is automatically changed to is blank.