Entering Advanced Search Criteria

An advanced search can be an extremely powerful way of extracting very specific data from the database. An advanced search is available on membership, group, and activity lists, and is the only search used for reports.

Unlike a basic search that gives you a set of predefined fields to pick from, a search using advanced criteria is built one field at a time, with each field represented by a new row on the advanced search criteria list.

Each row represents a specific criteria or "rule" that a record must meet in order to be retrieved. If you create membership criteria that says Last Name = "Smith", then only membership records with "Smith" in the Last Name field will be found.

This is an example of the advanced search on the Membership List:

This search will require four things from each record retrieved:

•	The only address data looked at will be the address marked "Current" (this option is only available in MemberTies Professional, since a member can have multiple addresses).

•	The member must have a positive dues balance

•	The End Date on the record must be prior to today's date

•	The city (on the current address) must be Chicago.

Building Criteria

Criteria is created row-by-row, with each row representing another criteria (you can think of it as a "rule") that must be true for a given record to appear on the report. For example, if you are running a membership report and use a criteria like, Last Name = Smith, then only membership records with "Smith" in the Last Name field will be on the report.

1	Start a new criteria line by clicking the Add icon. A new blank row will appear.

2	Click the dropdown arrow in the Field Name column to open the selection window. Then, choose the field whose data you want to check with this criteria. For example, if this criteria will restrict the report to members in a certain city, expand the Address section and choose "City."

3	Click the dropdown arrow in the Operator column to open the selection window. Then, choose the method that will be used to compare the data to the selected field. For example, if you want the city value to be equal to, "MyTown", you would choose "Equals".

4	Click the dropdown arrow in the Value column to open an appropriate data-entry window. Then enter the appropriate value to be compared to the selected Field.

The criteria also list includes options for left and right parenthesis, and a logical AND/OR option for joining rows of criteria. These options make complex queries possible, but also make it possible to accidentally retrieve every membership record when you only wanted one or two. It is very important to look carefully at the positioning of parenthesis, especially when using an OR option.

Using Parenthesis

The advanced criteria list includes options for left and right parenthesis, and a logical AND/OR option for joining rows of criteria. These options make complex queries possible, but also make it possible to accidentally retrieve every membership record when you only wanted one or two. It is very important to look carefully at the positioning of parenthesis, especially when using an OR option. For example, consider this criteria:

State = Texas AND

(City = Mytown OR

City = YourTown)

This will require a state of Texas, and either a city of Mytown or a city of YourTown, because the AND refers to all of the criteria grouped by the parenthesis. However, if the parenthesis were omitted, the criteria would look like this:

State = Texas AND

City = Mytown OR

City = YourTown

This can produce unpredictable behavior, because now it essentially says, "state of Texas and city of MyTown, or the city of YourTown without regard to the state". Thus, records could be returned for YourTown, Washington, and YourTown, Oregon, as well as YourTown, Texas.

Special Expressions using LIKE and NOT LIKE Operators

% The percent sign is used in conjunction with the word "LIKE" to search for data that begins, ends, or contains specific text or numbers. For example, if you selected Last Name as the field, and LIKE as the operator, you might enter any of the following in the Value field:

•	"Smith%" to get "Smith", "Smithe", or "Smithington.

•	"%burg" to get "Sederburg", "Frankenburg", "Hidelburg"

•	"%der" to get "Sederburg", "Fender", and "Derston"

You can also use the opposite of LIKE, which is NOT LIKE, to reverse the search requirement.

Complex Expressions Using LIKE and NOT LIKE Operators

If you are comfortable with the concepts of LIKE and NOT LIKE, the following table provides several advanced examples of ways to use wildcards and expressions for even more precise queries. Note that each expression below would be preceded with LIKE or NOT LIKE.

Expression	Searches for...	Sample Return Data
Mc%	Names that begin with the letters "Mc"	McEvoy
%er	Names that end with "er"	Brier, Miller, Weaver, Rayner
%en%	Names containing the letters "en"	Pettengill, Lencki, Cohen
_ish	Four-letter names ending in "ish"	Fish
Br[iy][ae]r	Brier, Bryer, Briar, or Bryar	Brier
[M-Z]owell	Names ending with "owell" that begin with a single letter in the range M to Z	Powell
M[^c]%	Names beginning with "M" that do not have "c" as the second letter	Moore, Mulley, Miller

As illustrated above, the % (percent) symbol will be replaced by any number of characters, whereas the _ (underscore) symbol can only be replaced by one character.

Text within brackets ( [ ] ) will be required unless preceded by the carat (^) symbol, in which case the text is disallowed.

Sample Searches - Click to see several examples of search criteria.

Address Criteria

When using MemberTies Professional, if you use address fields as part of your criteria, i.e., "City = MyTown", and your membership records often include multiple addresses, you may want to use a special criteria field called "Address to Use". With this criteria, you can tell the report which address you want it to look at when matching the City value.

If you don't use this criteria, the system will automatically assume you want to use the Current Address whenever it needs to look for address data. That means if a member has a Home Address in "MyTown" and a Summer Address in "YourTown", the record won't be retrieved unless the Home Address is marked as Current on the membership record.

Include in Mailings, Include on Reports

These two checkboxes are available on membership records in case you want to use them as report criteria. You may select either of these fields for criteria if you wish to use them. If you do not use them, i.e., if you don't include the "Include on Reports" criteria, it won't matter whether the checkbox is selected or not on a member record.

To Save and Reload Search Criteria

See Saving Advanced Criteria.