Field
Select the field on which you want to base the condition.
Comparison
Select the option to control how the field value is compared to the Compare To value. Most of the comparison operators are straightforward. Here are the notable exceptions:
Like
Like allows you to use wildcards to perform pattern matching on text fields. For example, you could specify Name Like *Electric* to return all vendors that have the word "Electric" in their names.
Like is not available for non-text fields, such as date and number types. The functionality of Like is restricted when working with workbook functions because of limitations imposed by Microsoft's filtering technology. For example, you can not create "ends with" patterns such as *Electric because a filter can not start with a wild card unless there is another wild card later in the pattern.
A complete description of how to use the Like operator is beyond the scope of this help. Event 1 does offer training that includes the use of the Like operator in our Workshop class. Alternately, there are numerous tutorials available on the web.
Not like
This option is not available for workbook functions.
As you might expect, Not like produces the exact opposite results that Like does, i.e. it excludes records that match the pattern.
One of
This option is not available for workbook functions.
One of is handy when you want to concisely check against a list of possible values. Using One of is equivalent to using a series of conditions in an Or group, but much more intuitive and compact.
When you select the One of operator, the Value Type must be Literal Value.
Not one of
This option is not available for workbook functions.
Similar to the One of operator, the Not one of operator returns records only if the value is not in the list.
Compare To
Value Type
The value type specifies what kind of source will be providing the value.
| Type | Description |
|---|---|
| Literal Value | The exact value that you specify will be used. The value will not change unless edited. |
| Cell Reference | Supported for workbook functions only. Specify a cell address or named range and the field comparison will be made using the value in that cell. Use absolute addressing (dollar signs) or a named range if you want the cell address to remain fixed regardless of whether or not the function is copied or moved. Use relative addressing (omitting dollar signs) if you want the cell to be relative to the function's position, for example when you are going to copy the function down and want the value to correspond to each row. See Excel's help for more information on how to use absolute and relative cell addressing. |
| Database Field | Supported for Queries only. This option lets you select a field whose value you want to compare to the field you selected for Field. For example, you may want to find records where the Actual Start Date is the same as the Estimated Start Date. This feature will become more important in future versions of the software when multiple tables are supported. |
| Parameter | Supported for Queries only. The comparison value comes from a cell in your Excel workbook. If you specify a valid cell address or named range, then the wizard will automatically bind the parameter to that cell with "Automatically refresh" turned off. If instead you'd rather specify the cell by clicking, enter a name (that is not a named range) for the parameter and Excel will prompt you when the wizard is finished. |
Value
The value is compared against the selected field.
- For True/False fields, use the checkbox to specify the value.
- For date fields, type a date or drop down the calendar to pick a date.
- For other fields, type a value or select from existing values by dropping down the list and clicking on the value you want. When you use the One Of operator, you can click on existing values or click the + button to add a value that you type. To remove a value from the list, select it and click the X button.