Search Examples

This topic provides various examples of more complex Advanced searches, as sell as ways to use complex expressions in Basic searches.

Complex Expressions

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.

These can be used in any Basic search window, or when using LIKE or NOT LIKE in an Advanced search criteria entry.

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.

The following examples illustrate various uses of the Advanced search criteria capability:

Finding Expired Members

This search illustrates what it might look like to search for all members whose membership (and probably their dues) have expired.  Since MemberTies assumes that you will reset the End Date to a point in the future when someone pays their dues, it can also be assumed that anyone with an End Date in the past is probably past due.

The Status on the first row displays "Multiple Statuses," because we have selected each status that pays dues. The value for End Date displays as "Current Date," meaning that the date in the query will always be "today." The system takes care of replacing the date for you when the search runs.

With these settings, this search will only find records that pay dues and have an End Date in the past.

Note: Using MemberTies Professional, you can post dues as debits and credits.  Therefore, it might be equally logical to assume that any member with a Dues Balance less than 0.00 is past due.  You could accomplish this using the Dues Balance criteria field name.

Using a Date Range

These examples illustrate two different ways to search for all records with a Start Date that falls within the month of November.

Example (1) uses the ">="  and "<=" operators to request any date that is greater than, or equal to November 1, AND less than or equal to November 30.

Example (2) accomplishes the same thing by using the ">" and "<" operators to request any date that is greater than October 31, and less than December 1.

Both queries will only return records with a Start Date within the month of November.

Using a Relative Date

This example illustrates how to search for a relative date, i.e., all records with an End Date that occurs within the next 30 days.

Notice that on the Enter Criteria Value window, the Current Date option is selected, and 30 is entered into the Adjusted field.  This tells the system to find records with an End Date starting today, and going +30 days into the future.

Likewise, you could enter -30 to find records with an End Date within the previous 30 days.

Correct vs Incorrect Use of "AND" and "OR"

These examples attempt to retrieve all members of the Johnson family living in Berryville and Birmingham.

Example (1) is incorrect, because it is telling the system to retrieve anyone in Berryville, and then only Johnson family members in Birmingham.

Example (2) solves this problem by simply surrounding the city portion in parenthesis. Now the query tells the system the records must have city of (Berryville or Birmingham), and a last name of Johnson. This query will bring back a much smaller set of records.