The Filter page of the report wizards allows you to see the conditions that make up the filter, add new conditions, and edit or remove conditions.

Adding a filter condition

To add a new condition to the filter, click the left button. The new condition appears within the filter group you clicked the button for. The condition's bar shows the settings for the condition: the field name, the operator, and the values.

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

The connection button, which does not appear if this is the first condition for your filter group, 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 by clicking the button. See the Creating a Filter topic for a discussion of how connections work. The black brace shows which conditions are connected with a particular connection.

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 Fields drop-down list allows you to determine which fields are displayed when creating a filter condition. Show all fields means display all fields whether they are included in the report or not, Show selected fields only means display only those fields in the Selected Fields list for the report, and Show selected tables only means display only those tables being used 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 Boolean (true or false or yes or no) 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 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.

When you start typing a value, Report Writer checks whether there are any values in the field the filter condition is for that start with what you've typed. If so, it displays the choices in a drop-down list. For example, if your database contains cities named San Cristóbal and San Francisco, you'll see the following when you type "San":

If you're not sure what values appear in the field or want to select a value without having to type it, click the button. A list of unique values from the field appears in the Field 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, type the desired date in the format YYYY-MM-DD (or YYYY-MM-DD HH:MM:SS if Use date only is turned off), where YYYY is the year, MM is the month number, DD is the day number, HH is the hours, MM is the minutes, and SS is the seconds; for example, January 4, 2014 is 2014-01-04. To display the date without the time, turn on Use date only. Instead of typing the date, you can click the button at the right of the text box to display a calendar. 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 at the top of the calendar to display a list of months in the selected year you can select from. Clicking a month moves the calendar to that month. Click the left or right arrows to move to the previous or next year.

Click the year at the top of the calendar to display a list of years in the selected decade. Clicking a year displays the months for that year. Click the left or right arrows to move to the previous or next decade.

If Use date only is turned off, the bottom of the calendar has a button. Click this button to display the time with up and down buttons to make it easy to change the hours and minutes.

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, select Field from the Compare to option (the default is Value, which means a value is used). Select the field to compare against in the drop-down list that appears in place of the value text box. Note that this only allows you to select fields from the same table and the same data type as the field you're filtering on; for example, if you choose a date field, only date fields from the same table appear in the list. Also note that Compare to isn't available if you choose the is blank, is not blank, is Yes, is No, is one of, or is not one of operators. To compare a field in one table to a field in another table, use a database 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 either Database Expression or Expression from the Compare to option. The difference between the two is that Database Expression is an expression the database engine evaluates and Expression is an expression that Report Writer evaluates. Typically, you'll use Database Expression to specify a field or a function that you know the database engine can evaluate and you'll use Expression for any other type of expression.

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 AddDays(Now(), -7) for the first expression and Now() for the second expression. Since Now() gives today's date and AddDays() adds the specified number of days to a date, AddDays(Now(), -7) is seven days ago, so this gives all records with the date being between one week ago and today. The Expressions and Functions Reference topic has a list of the most useful functions.

Note that the "is one of" and "is not one of" operators are only available if Compare to is set to Value.

By default, filter conditions are displayed in the header of the report. If you don't want a particular condition to be displayed, turn off the Display in report header setting. This is 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.

The condition is displayed in the report header as Field Operator Value, such as "City equals San Francisco" or "Active Is Yes." If you want to customize the description, turn on Custom description and enter the desired description. For example, "Active customers" might be a more meaningful description than "Active Is Yes." If you want to use an expression rather than fixed text, surround the expression with "{" and "}" (without the quotes). Note that the expression must evaluate to a string value, so use the Str() function if necessary to convert non-string values such as dates and numbers to their string equivalent.

Here's an example of an expression that displays "Customers in " (such as "Customers in Germany" if Germany is the value chosen for the filter condition) in the report header:

{"Customers in " + GetConditionValue(1)}

GetConditionValue is a built-in function that returns the value for the condition as a string; specify "1" for the first value, "2" for the second value, and so on (a condition only has more than one value if the "is between" or "is one of" operators is used).

After you've finished with the condition, you may wish to click its bar to collapse the condition settings so they don't take up as much room.

Adding a filter group

As discussed in the Creating a Filter topic, filter groups are used to group filter conditions that use the same operator. Filter groups are connected together using another connection. So, to get all customers from San Francisco or Los Angeles where the sales amount is more than $50, use one filter group of "City equals San Francisco" connected with OR to "City equals Los Angeles" and another filter group of "Sales is greater than 50." The two groups are connected using AND.

The only complication is the order in which you create conditions and groups. If you know you'll need at least one filter group, start by creating one by clicking the right-most button. Don't worry if you didn't realize until later that you needed a group; we'll show you later how to rearrange conditions into groups.

To use the example of customers from San Francisco or Los Angeles where the sales amount is more than $50, do the following:

• Click the right-most button to create a new filter group.

• Click the left button in the new group to create a filter condition and change the settings for the condition to City equals San Francisco. The filter page should look like this:

  

• Click the left button in the new group again to create another filter condition and change the settings for the condition to City equals Los Angeles. Click the connection button (which defaults to And) to change it to Or. The filter page should now look like this:

  

• Click the right-most button in the top (not the new) group to create a filter group.

• Click the left button in the new group to create a filter condition and change the settings for the condition to Total Price is greater than 50. The filter page should now look like this:

  

Rearranging Conditions

Suppose you missed the first step, creating a filter group, in the instructions above, and then realized you needed a group once you'd created the two conditions on City. Now adding a filter group adds it under the second condition, which isn't where you want it. How would you fix that? The solution is to rearrange conditions by dragging them.

Starting from just the two conditions, do the following:

• Click the right-most button create a filter group.

  

• Drag the bar for the first condition to the space inside the new group that indicates you can drag a condition to it.

  

• Drag the bar for the second condition below the first condition.

  

• Now that you've created a group for the two City conditions, you can continue on with the instructions to add the second group and the condition on Total Price.

Editing a condition

To edit a condition, click its bar to expand it if necessary and change any of the items that make up the condition.

Removing a condition

To remove a condition, click the button in its bar.