Choosing Records Using Selection Criteria

Screen ID:

Screen Title:

Panel Number:

CU*BASE Report Builder (Query)

Select Records

QRY001

$images\qry001g_shg.gif$
Click here to magnify

This screen appears when you choose the “Choose records” option on the Report Builder Main Menu. It will also appear whenever you select one of the “canned” Query reports.

Helpful Resources

Learn how to perform Query-related tasks in CU*BASE.

Screen Overview

The query tool is very powerful and handling it incorrectly can lead to mistakes in accuracy and processing. Most significantly, incorrect joining (matching) data from multiple files, or incorrect filter settings can lead to significant errors. If you are a new user of query, please be extra vigilant about checking your work (preview the report) after each change you make and contact an expert at CU*Answers with your questions when needed.

This screen is used to choose which records should be included in the report. For example, you may want to see only records which fall within a certain date range or have a certain amount in a balance field. In the following example, only accounts in branch 01 with a balance between $1,000 and $5,000 will be included:

$images\qry001intopicg_shg.gif$

See the below for instructions on entering selection criteria. When done specifying criteria, use Enter to save changes.

If this report uses multiple files, the file ID must precede the field name (for example, T01.BRANCH) for any fields which may occur in both files. Click for details about using multiple files.

NOTE: Many of the CU*BASE standard “canned” Query reports use this selection screen to allow you to specify which records should appear on the displayed report. For these types of reports, this is the only screen which will appear; no other parameters can be modified. Using Enter in this case will simply run the report and display the results on the screen.

Specifying Query Conditions

Option	Description
Combine And/Or	This field appears on all but the very first criteria item. Choose AND to add an additional condition. A record must match both conditions in order to be displayed. For example, to search for transactions that occurred on a specific date, and were performed by a specific teller ID, use AND. This is the most common option used. Enter OR to add a separate condition. Any transaction that matches either or both of the conditions will be displayed. For example, to search for records in either Grand Rapids or Kentwood, use OR. IMPORTANT: Special rules apply when combining “AND” and “OR” conditions in the same report. For example, say you wish to locate debits of $1,233.45 in GL account number 739.00 (an “AND” condition) or credits of $1,233.45 in the same GL account (an “OR” condition). The conditions would be entered as follows (field names are fictitious for this example): DEBIT EQ 1233.45 AND GLACCT EQ 739.00 OR CREDIT EQ 1233.45 AND GLACCT EQ 739.00 The system first gathers all debits of $1,233.45 for GL account 739.00, then gathers all credits of $1,233.45 for GL account 739.00. A common mistake is to exclude the last condition, which would result in debits of $1,233.45 for GL account 739.00, as well as all credits of $1,233.45.
Field Name	Enter the field which should be searched. The field name must exactly match the fields listed on the bottom half of the screen. To see additional fields, click the blue arrows beneath the bottom list (or click anywhere on the list then use Page Down and Page Up keys). If necessary, use Display Names Only (F11) to display a description next to each field name. This is a toggle: click the button again to display field names only.
Comparison	Enter one of the following tests to indicate how the value should match the transaction record. (You may type the shortcut, such as EQ, or choose from the pull-down list.) EQ Equal to NE Not equal to LE Less than or equal to GE Greater than or equal to LT Less than GT Greater than RANGE Between one value and another value LIST Equals one of the values in the list NLIST Does not equal one of the values in the list LIKE Starts with, ends with or matches the pattern of NLIKE Does not start with, end with, or match the pattern of
Criteria	Enter the value you are searching for, using the following rules as a guide: Any fields containing alphanumeric text must be entered with single quotes surrounding the value. For example, if searching for teller ID 89, the value must be entered as '89' in the value field. To find out whether or not a field requires the quotes, check the “Dec” column next to the field name and description. If any number (even a zero) is displayed, no quotes are needed. A blank in that column indicates that quotes will be necessary. Except for whole numbers, values for numeric fields must be entered with a decimal point. If any number is displayed in the “Dec” column next to the field name and description, the field is a numeric field. For example, if using the Transaction Amount field, enter the value with a decimal point and two digits beyond the decimal (4537.95). Commas are not allowed. To search for text that begins with a specific set of characters, use the % sign to indicate any characters. For example, if you wish to search for transaction descriptions that begin with the characters “NSF,” the condition would be entered as follows: 'NSF%'

Using ISO Date Fields to Choose Records

With the File Expansion Project in 2014, CU*Answers began reformatting date fields into an easier to use ISO format. Previously, dates in the system could be difficult to work with as selecting a range of dates required multiple steps or often creating new fields to extract portions of the date (see the section below). With ISO formatted dates, distinguishable by their CCYY-MM-DD output and 'L' value in the Decimal column, selecting a range or sorting by the date is much easier.

Inputting the Selection Criteria

When using ISO dates, the date must be entered in single quotations (like a text field). Criteria can be entered with different formats, including with or without leading zeros (with the exception of MM/DD/YY), but they must be in single quotes. You can simply enter your date range or use the greater/less than comparisons to find all records between, after, or before the defined dates. In the example below, we are selecting accounts opened Dec 31, 2010 or earlier, between Jan 1 and Dec 31, 2012, or since Jan 1, 2014.

Selecting Records with No Date

For the ISO format, a "null" (blank) date is represented as 0001-01-01. In the example below, we select all loan accounts that have not yet had a payment made (i.e. no last transaction date):

Tips for Date Ranges Crossing 19xx ® 2000 on Non-ISO Formatted Dates

If Year is a separate field (CCYY):

$images\selectsample2_shg.gif$

If Year is a separate field (YY):

$images\selectsample3_shg.gif$

If Date is a combined field (MMDDYY):

First define a result field using the DIGITS expression to convert the date field to text. Then define a second result field that uses use the SUBSTR expression to allow the 2-digit year portion of that field to be used separately. (Click here for information about defining these types of results fields.) Then define selection criteria using the defined year field:

$images\selectsample4_shg.gif$

If Date is a combined field (YYMMDD):

$images\selectsample5_shg.gif$