Use the options in this window to define or modify a condition that will limit the results of your function. A condition consists of three parts:
Column
Column List
The columns belonging to the selected table are displayed here. The first column provides user-friendly captions for the columns. The second column identifies the name of the column in the underlying database. You can sort this list by clicking the column caption of either column. Click once to sort in ascending alphabetical order. Click again, to sort in descending order, and click a third time to return to an unsorted list.
You can quickly locate a specific column in the list by doing one of two things:
- Start typing the first few characters of the column's caption.
- Press Ctrl+F to bring up the Find window, enter the text you are searching for, and click OK.
Comparison
Select the type of comparison you wish to do between the selected column's value and the value identified in the Compare to box.
Not every type of comparison may be available for each column dependent on the type of data in the column and the type of database.
Equal to | Condition is satisfied if the column value is equal to the value in the Compare to box. |
Not equal | Condition is satisfied if the column value is not equal to the value in the Compare to box. |
Greater than | Condition is satisfied if the column value is greater than the value in the Compare to box. For text values, the column value is considered greater if falls after the Compare to value alphabetically. For date values, the column value is considered greater if it falls after the Compare to value chronologically. |
Greater or equal | Condition is satisfied if the column value is equal to or greater than the value in the Compare to box. |
Less than | Condition is satisfied if the column value is less than the value in the Compare to box. For text values, the column value is considered less if it comes before the Compare to value alphabetically. For date values, the column value is considered less if it falls before the Compare to value chronologically. |
Less or equal | Condition is satisfied if the column value is equal to or less than the value in the Compare to box. |
Like | Compares the column value to a pattern in the Compare to box that may contain literal characters as well as wildcard characters. The specific wildcard characters to use may be dependent on the type of database. Generally, an asterisk (*) or percent symbol (%) is used to designate a set of zero or more characters of any character. A question mark (?) or underscore (_) is generally used to designate a single wildcard character. The pattern to match against may either be a literal value or a value contained in a cell. |
Not like | Similar to Like except that condition is satisfied only if the column value does not match the pattern identified in the Compare to box. |
One of | Allows you to enter a set of one or more literal values in the Compare to box. The column value must match one of the values in the set in order for the condition to be satisfied. |
Not one of | Similar to One of except that the condition is satisfied only if the column value is not contained in the set of values in the Compare to box. |
Compare to
Value Type
Click the dropdown arrow in this box to select the type of value to compare the column value with. The options are:
Literal Value | Select this option if you want to enter a literal value (not tied to a cell in the worksheet) so that the condition will always be based on the value you enter here. |
Database Field | Select this option if you want to compare the value in the selected column to the value in another column. |
Cell Reference | Select this option if you want to compare the column value to a cell in your worksheet. |
Value
- When the Value Type is Literal Value, you can specify the value here. The representation of the value may be a text box, a checkbox or a date selector depending on the type of data contained in the column. Click the dropdown button to view and select from a distinct list of values that exist in the database in the selected column.
- When the Value Type is Database Field, a list of available columns will be displayed. Select the table and column that you wish to compare the column on the left with.
- When the Value Type is Cell Reference, specify an Excel cell reference here. For example,
$A10
orSheet2!$A$5
. If your workbook or worksheet contains named cells, you may click the drop-down button to select a named cell. - When the Coparison is One of or Not one of you may specify one or more literal values here. Click the drop-down button to select a literal value from a list queried from the database. You may also type values and click the plus button to add them. Click the X button to remove values from the list.
Current Value
This box appears only when the Value Type is Cell Reference. This box shows the value contained in the cell identified in the Value box. You may change the value of the cell by typing in this box.
Table
If your query includes more than one table, the first option in this frame will be a drop-down list for the Table. If your query is based only on one table, this drop-down list will not be present. When present, you can click the drop-down list and select the desired table. The list of available columns will show the the columns that belong to the selected table.