In Query, there are two types of operators:

  • Criteria operators, which tells the query how to compare the field to the record. An example is equals. Note: Not every operator is available for each field.
  • Combining operators, which allows you to combine multiple lines of criteria (e.g. filters) in a query. 

Note: For the operators when merging queries, refer to BB137733

Criteria Operators 

 

Operator DefinitionExampleUse Wildcards?
EqualsRecords must have the exact entry you define.Phone Type equals Home selects only records with Home in the Phone Type field. 
Does Not EqualRecords must not match the entry you define.Phone Type Does Not Equal Home excludes every record that has only the Home phone type. (If the record has only the Home Phone Type, it is excluded from the query. If the record has multiple phone types and one is Home, the record is included in the query). 
Greater Than/Greater Than or Equal ToRecords must have a value greater than the one you define.Payment Amount greater than $500 only selects records with payment amounts of more than $500 (e.g. $500.01 or more). The greater than or equal to operator also includes $500 payments. 
Less Than/Less Than or Equal ToRecords must have a value less than the one you define.Payment Amount less than $500 selects only records with a payment amount less than $500 (e.g. $0.00-$499.99). The less than or equal to operator also includes $500 payments. 
One OfRecords must have at least one of the entries you define.State one of NC, SC, FL selects records with an address in one of the three states. 
Not One OfRecords must not have any of the entries you define.State not one of NC, SC, FL selects records without any of the three states. 
BetweenRecords must fall within the range you define. This criteria operator is inclusive.Payment Date between 1/1/08 and 12/31/08 selects records with payment dates between 1/1/08 and 12/31/08. Payments on 1/1/08 and 12/31/08 are also included. 
Not BetweenRecords must not fall within the range you specify. This operator is exclusive.Payment Date not between 1/1/08 and 12/31/08 selects records with payment dates before 1/1/08 and after 12/31/08. Payments between 1/1/08 and 12/31/08 are not included. 
BlankRecords must have a blank in the field you specify.First Name is blank selects records with an empty First Name field. 
Is Not BlankRecords must have an entry in the field you specify.First Name not blank selects all records with an entry in the First Name field. 
ContainsRecords must contain the defined value anywhere in the field.Last Name contains L selects records with the letter L anywhere in the last name: Miller, Lawrence, Ball, Clover, etc.Yes
Does Not ContainRecords must not contain the defined value anywhere in the field.Last Name does not contain L selects records without L anywhere in the last name field. It would exclude Miller, Lawrence, Ball, Clover, etc. Yes
Begins WithRecords must have an entry beginning with the value you define.Last Name begins with Bell selects records whose last name begin with Bell: Bell, Bellmont, Bellingham, etc. It would not include Campbell.Yes
Does Not Begin WithRecords selected must not have an entry beginning with the value you define.Last Name does not begin with Bell selects records whose last name does not begin with Bell. It would exclude Bell, Bellmont, Bellingham, etc. It would include Campbell.Yes
LikeThis operator and the wildcard characters are particularly useful when you are not sure of the spelling of a name or if you suspect something might be misspelledLast Name like B?rd selects records with a similar spelling: Berd, Bird, Burd, or Byrd. Yes
Not LikeSelects the records that are not spelled like the entry you define.Last Name not like B?rd excludes records with a similar spelling: Berd, Bird, Burd, or Byrd.Yes
Sounds Like Selects records based on a field for which you do not have the exact spelling.To add a note about a conversation you had over the phone with a constituent who says their name is Smith. However, their name is actually spelled Smyth, which sounds like Smith. When you query, if you search for "sounds like" Smith, the software will find their record.Yes
AskPrompt the user at run-time.Lets you postpone the selection of specific values for your criteria fields (filters) until the time you actually run the query or use it in another area of the program.  Yes

 Combining Operators 

 

OperatorDefinitionExample
ANDThe default combining operator in Query. Records must meet both criteria to be selected.Last Name equals Smith AND State equals SC Selects all constituents whose last name is Smith and whose state is SC. John Smith is only selected if he also has a state of SC.
ORRecords can meet either criteria to be selected.Last Name equals Smith OR State equals SC Selects all constituents whose last name is Smith and selects all constituents with a state of SC. For example, John Smith is selected because his last name is Smith -- he does not need a state of SC. Mary Jones is selected if she has a state of SC
Parentheses ( )Use parentheses to group query criteria when necessary. For more information, refer to How to use parentheses in query (BB24257).