<< Click to Display Table of Contents >> Navigation:  Searching for Records > Search Examples

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

Here are some common searches:

Wildcards - Special Expressions using LIKE and NOT LIKE

The percent symbol (%) the default "anything" wildcard symbol for the database. However, if the Allow Asterisk database preference is turned on, the asterisk can also be used.  For this documentation, we will use the percent symbol in our examples.

The wildcard symbol is used to search for data that begins, ends, or contains specific text or numbers. For example, you might enter any of the following in the Last Name field:

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

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

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

The word "LIKE" is implied in searches like this.  In other words, the system assumes you mean "LIKE %widget%" whether you type the word "like" or not.  However, if you want to search in the opposite manner and find words that do not contain widget, you have to type "NOT LIKE" before the search. i.e., "not like %widget%" would find any record without the word widget in the company name.

Note: If you use a "%" symbol in a search field, the system will assume the word LIKE should be used with it.

> < <> The greater than, less than, and not-equal-to signs are used at the start of data to indicate a relationship to the data following the sign. These symbols can be used with letters or numbers. For example:

•>300 in the Member ID field would get all records with a Member ID numbered 301 or larger (if the Require Numeric IDs preference is on).

•>a  used in the Last Name field would return all records that have a Last Name beginning with b-z.

•<>Smith in the Last Name field would get all records with a Last Name other than "Smith," i.e., "not equal to Smith".

•< 12/31/1999 in the End Date operator and date field would get all records with an End Date prior to 12/31/1999.

 

Complex Expressions Using LIKE and NOT LIKE

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 while "LIKE" is implied as described above; you must precede the expression with "NOT LIKE" to search for the opposite.

 

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 of our dues-paying statuses. The value for End Date displays as "Today," meaning that the date in the query will automatically be replaced with the current date 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 make more sense 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: Use a beginning and ending date as a date range.

Example 2: Use the month and year versions of the Start Date to restrict to one month.

Example 3: If the year isn't important, use only the month of November.
 

Example 4: Use the range to specify both dates on a single entry.

 

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 30 is entered into the Adjust 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 who live in Dallas, TX or Houston, TX.

 

Example (1) is incorrect, because it is telling the system to retrieve anyone in Texas who live in Dallas And Houston at the same time.

Example (2) solves this problem by simply surrounding the city portion in parenthesis and using OR.  Now the query tells the system the records must have city of (Dallas or Houston), and a state of Texas. This query will bring back a much smaller set of records.