Defining Selection Criteria

The fifth window in the Query Wizard set asks What is the Criteria for selecting the information? It allows you to narrow the amount of information displayed by specifying value ranges for selected elements.

The top half of the window displays criteria you have entered. The bottom half of the window provides fields for entering and selecting elements comprising a selection statement. At a minimum, a statement consists of a column, operator, and value/criteria. Optionally, you can connect two or more conditions together using and or or values from the Logical field. As another option, you can use the parentheses contained in the dropdown lists at the left and right edges of the entry bar to connect two or conditions without affecting the remainder of the statement. The parenthesis dropdown lists give you the option of nesting conditions to four levels.

Available Operators

In addition to standard operators like equal, not equal, equal to or greater than, etc., the Query Wizard offers additional operators to make your queries more powerful.

Allows you to search a period of time between two dates. For date columns, you can enter expressions like today, today -30, or today +60. When executing your query, Tabware substitutes the current calendar date for today and then adds or subtracts days accordingly.

Note: When using date range for queries, if the field is left blank, TabWare defaults to the system date.

Allows you to specify multiple values/criteria in one statement. The understood operator is or. For example, you can search for something specific in multiple areas—Area in list 001 or 002 or 005.

Allows for the selection of a field that contains no entry.

Allows the use of trailing wild cards in the criteria. For instance, you can search for all areas which begin with the number 2. This would include areas 20, 200, 201, 250, 2500, etc.

Applies the same principle as starts with, except that the anything part ends the criteria. This could be used to search for information grouped by all supervisors whose last name is Jones.

Enables you to build a query that searches for particular characters or numbers within a field. For example, you could search for all equipment descriptions containing the word pump.

Allows you to specify multiple key words on which to search. The understood operator in contains listis and. For example, an item search where Description contains list pump, valve, 6 inch would find any item which has a description containing all three of these key words.

Allows exclusion of a particular period of time.

Allows exclusion of items from multiple values/criteria.

Provides for the inclusion of a field that contains any entry.

Excludes criteria that does not begin with a particular value.

Excludes criteria that does not end with a particular value.

Excludes criteria that does not contain a particular value.

Note: You need to take special precautions when building a query that excludes data, such as Account <> 200 or Area does not contain 100. If you do not build the selection criteria properly, the query will not return the expected data. For example, for the Work Order query Account <> 200, TabWare will return work orders in which the account field is not empty and is not equal to 200. Work Orders with an empty account field will not be displayed. To see every work order with an account number not equal to 200, the selection criteria should be as follows: Account <> 200 OR Account is empty.

Using the Selection Criteria Popup

When entering information in the Column field, if you select an element for which a valid value has been defined, a search icon will display to the right of the Value/Criteria field. If you click on the icon, a popup window displays a list of valid values for that particular element.

Using Custom Elements

There will be a second selection criteria window displayed if, in the second window of the Query Wizard set (What Information do you want to see in this query?), you selected custom elements for display.

Use the second selection criteria window to select custom elements to append to the first set of selection criteria. Due to database design constraints, the logical operator and cannot be used with criteria for custom elements.

Note: If you need to add custom elements to your selection criteria, it is best to select on the same custom elements you have chosen to display. For example, in the Equipment Search, if you are displaying Horsepower and FrameSize and you need to select by one of these custom elements, you should define selection criteria for both elements. If you enter a selection criterion for only one element (e.g., Horsepower >=10), results can be misleading. In this case, you will see all equipment rows for which Horsepower >=10. In addition, you will see all equipment rows that have any non-empty value for FrameSize, regardless of the equipment’s horsepower. In effect, the search is saying the following:

Display equipment rows for which:

a. A value for Horsepower exists and Horsepower >=10

or

b. Any non-empty value for Framesize exists.

Note that condition b can return equipment rows for which Horsepower is not entered, or has been entered but is less than 10.

1.	Click the search icon located next to the Value/Criteria field. A popup window displays.
2.	Optionally, enter a full or partial value in the selection criteria field at the top of the window and then click the Search button.
3.	Highlight a value in the search results window.
4.	Click OK.