Adding a Condition
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.
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. When the calendar is not displayed, you can press one of the following keys as shortcuts:
|M||First day of this month ("M" is the first letter of "month")|
|H||Last day of this month ("H" is the last letter of "month")|
|Y||First day of this year ("Y" is the first letter of "year")|
|R||Last day of this year ("R" is the last letter of "year")|
|Alt+Down Arrow||Display the calendar|
|+ or Up Arrow||Next month, day, or year, depending on which is selected|
|- or Down Arrow||Previous month, day, or year, depending on which is selected|
|Home||First month, day, or year, depending on which is selected|
|End||Last month, day, or year, depending on which is selected|
|/ or Right Arrow||Move to the next part of the date|
|Left Arrow||Move to the previous part of the date|
When the calendar is displayed, you can select a date by simply clicking it. Click the left or right arrows to move to the previous or next month. Click the month to display a list of months you can select from. Click the year to display up and down buttons so you can change the year. To select today's date, click the word "Today." You can also press one of the following keys as shortcuts:
|Page Up||Previous month|
|Page Down||Next month|
|Ctrl+Page Up||Previous year|
|Ctrl+Page Down||Next year|
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.
If you want to be prompted to enter the values when you run the report, turn on the Ask at runtime option.
To compare a field to another field rather than a value, click the More button to expand the dialog and display the Compare to option (the button then appears as Less). Select "Field" from this 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 from the same table as the field you're filtering on. To compare a field in one table to a field in another table, 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, click the More button and 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.
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.
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. Note: if you don't enter a value to compare to the field, the operator is automatically changed to is blank.