Entering Basic Search Criteria


Table of Contents   Previous Page  Next Page    

A basic search means you are going to simply fill in the predefined fields with the values you want to match in the records you are searching for.  You do not have to use all of the available fields, in fact, if you don't use any of the fields you will retrieve all records (because you aren't trying to restrict the data to anything in particular).

The main thing to remember is that you should not enter any search value unless you really want EVERY record found to contain that value.  For example, if you want to create a mailing list for all of your members, you shouldn't put a value in the City Name field, because that will restrict the records found to only those with the specified city name on their address.

Each search window displays the most commonly used search fields If you need to search using a more obscure field, or using more specific or complicated criteria, you should perform an advanced search if one is available on the window you are using.

Note: A common question is, "How do I search for a blank (something). i.e., all members with a blank email address?"  To do this, you must use the Advanced Search, and leave the value for the email criteria empty.  The basic search cannot search for a blank, because it only searches for values that have been entered in the fields, and you can't enter "nothing."

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

Notice in the above example that a popup menu has been opened to help enter some special search criteria.  This popup menu is displayed by clicking the right-hand mouse button on the desired field (in this case, the Company Name field).  After choosing "Contains" from the popup menu, you simply change the word "text" in the field to be whatever portion of a word you are trying to match. For example, to find all companies with "widget" in their name, the field would be left as "LIKE %widget%" as shown above.

Using Barcodes or Magnetic Stripe Readers

If your members carry membership cards that contain a barcoded Member ID, you can scan it into the Member ID field to quickly search for that member.  If you use the member's US Driver License number as the Member ID, you can also scan a driver license into the Member ID field, and the system will pull the driver license number out of the data and search with that.  Note that only cards meeting the AAMVA specification are supported.  If you scan a driver license and nothing happens, the card is either too old, or doesn't follow the spec (California cards, for example).

Special Expressions using LIKE and NOT LIKE

% 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, 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"
 

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

Note: If you use a "%" symbol in a search field, the system will assume the word LIKE should be used with it. Therefore, "LIKE smith%" is equivalent to simply "smith%". But if you want to use NOT LIKE, you must actually specify it, i.e., "NOT LIKE smith%".

> < <> 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 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 each expression below would be preceeded 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.

Multiple Criteria

Entering criteria in multiple fields enables you to build more complex searches.

For example to get all members who live in Dallas, TX, with a phone number beginning with 972 who joined prior to 01/01/1999, you would enter:

"Dallas" in the City field
"TX" in the State/Prov field
"LIKE 972%" in the Home Phone field
"<01/01/1999" in the Start Date field

 

The key thing to remember with multiple criteria is that there is an implied "AND" for each value.  So in the above example, the criteria reads, "city = Dallas AND state = TX AND home phone LIKE 972% AND start date < 01/01/1999". Every value must match for a record to be returned by the search.