Filter Tab, Query and View Designers

  • Article

Determines the conditions for selecting records as listed in the WHERE clause of the SELECT - SQL Command in the SQL window. You can load and save a query or view with a subquery in the WHERE clause. This tab appears in the Query and View Designers.

Mover box

Drag the mover box to move the selected item up or down within the grid.

  • Condition Button
    Click the double-headed horizontal arrow to edit the selected filter.

  • Field Name
    Specifies the first field of the filter condition. When creating a new filter condition, click the field to display a drop-down list of the available fields.

  • Not
    Reverses the condition to exclude records matching the condition.

  • Criteria
    Specifies a comparison operator:

    • Equal (=)   Specifies that field and the value in the Example field have the same value.

    • Like   Specifies that the field must include characters that match characters in the text in the Example field.

      For example, Customer.state Like O matches records from Ohio, Oklahoma, and Oregon.

    • Exactly Like (==)   Specifies that the field must match, character for character, the text in the Example field.

    • Greater Than (>)   Specifies that the field must be more than the value in the Example field.

    • Less Than (<)   Specifies that the field must be the same or less than the value in the Example field.

    • Greater Than or Equal To (>=)   Specifies that the field must be the same or more than the value in the Example field.

    • Less Than or Equal To (<=)   Specifies that the field must be the same or less than the value in the Example field.

    • Is NULL   Specifies that the field must contain a null value.

    • Is True   Constructs a WHERE clause containing only the left field where the field or expression evaluates to True, assuming the field or expression evaluates to a logical value.

    • Between   Specifies that the field must be greater than or equal to the lower value, and less than or equal to the higher value, in the condition appearing in the Example column. You must separate the two values in the Example field with the word AND. Visual FoxPro creates the query with the word BETWEEN.

      For example, Invoices.idate Between 05/10/97 AND 05/12/97 matches records for the 10th, 11th, and 12th of May, 1997.

    • In   Specifies that the field must match one of several values in the comma-separated list appearing in the Example field.

      For example, Customer.name In Al,George,Mary matches records in which the customer's name is Al, George, or Mary.

    • Exists   Specifies that the records returned contain the criterion in the Example field. When the filter condition specifies Exists, the filter condition evaluates to True (.T.) unless the subquery returns an empty set.

      In the following example, specifying Exists returns every customer that has an order:

      SELECT Customer.cust_id, Customer.company;
 FROM testdata!customer;
 WHERE Exists (select * from orders where customer.cust_id = ;
   orders.cust_id)

  • Example
    Specifies the value or text with which you want to compare the field. To match the text casing of the example, use the UPPER( ), LOWER( ), and PROPER( ) functions.

  • Logical
    Adds an AND or OR condition to the filter condition list.

  • Pri. (Priority)
    Specifies a number ranging from 0 to 99, which indicates the priority for logical operations. The number 0 indicates the highest priority, while the number 99 indicates the lowest priority.

    Setting priorities for logical operations assigns the order in which each logical operation is performed. In effect, the behavior is similar to using parentheses (()) to group operations. For example, suppose you specify a priority of 0 for a logical operation, a priority of 1 for a second logical operation, and a priority of 0 for a third logical operation. The first and third logical operations with priority 0 are performed before the logical operation with priority 1.

  • Insert button
    Inserts a blank filter condition above the selected condition.

  • Remove button
    Removes the selected condition from the query.

See Also

Reference

Filter Conditions for Queries and Views

Fields Tab, Query and View Designers

Group By Tab, Query and View Designers

Join Condition Dialog Box

Order By Tab, Query and View Designers

Update Criteria Tab, View Designer