Selection Criteria Examples

The following table lists sample criteria formulas for various data types.

These sample formulas refer to fields that may not exist in your data dictionary.

Data Type	Description	Examples
ID	There is only one field of this type. The ID field contains the main identifier of the profile.	Finds the profile where the ID equals 20020 ID="20020" Finds all profiles except for the profile where the ID 20020 ID<>"20020"
CHARACTER	Fields of this type contain a single line of text information, such as Name or Address.	Returns records where the person's last name is Nick. LastName = "Nick" Returns records where the LastName is alphabetically less than M. LastName < "M" Returns records where the LastName is alphabetically greater than or equal to M. LastName > = "M" The address field is blank. Address1 IS EMPTY The address field is not blank. Address1 IS NOT EMPTY The LIKE operator compares a character value to a pattern that can pattern matching methods: To match zero or more characters, use a percent sign character (%) as a multiple-character wildcard. To match any single character, use an underscore character (_) as a single-character wildcard. To match a single character within a specified range of characters, specify the range, such as [0-9], [A-E]. To match a single character NOT within a specified range of characters, specify the range, such as [^0-9], [^A-E]. To match a single character within a specified set of characters, specify the set within brackets, such as [012345] or [abcdef012345]. To match a single character NOT within a specified set of characters, specify the set within brackets, such as [^012345] or [^abcdef012345]. LastName starts with the letter M. LastName LIKE "M%" The address contains the word cherry. Address LIKE "%Cherry%" ZipCode is not a five-digit zip code. ZipCode NOT LIKE "[0-9][0-9][0-9][0-9][0-9]"
LOGICAL	Fields of this type contain TRUE/FALSE or YES/NO information.	The ReceivesFreeMilk field is equal to TRUE. The =TRUE part of the formula can be omitted. ReceivesFreeMilk=TRUE The ReceivesFreeMilk field is equal to FALSE. An alternative way to express this logic is "Not ReceivesFreeMilk." ReceivesFreeMilk=FALSE
NUMERIC, INTEGER, or SCORE	Fields of these types contain numbers. Numeric and score fields allow decimal places. Integer fields do not allow for decimal places.	The number of days absent equals 5. DaysAbsent=5 The number of days absent is less than 5. DaysAbsent<5 The number of days absent is greater than 5. DaysAbsent>5 The number of days absent is less than or equal to 5. DaysAbsent<=5 The number of days absent is greater than or equal to 5. DaysAbsent>=5 The DaysAbsent field does not contain a number. DaysAbsent IS EMPTY The DaysAbsent field contains a number. DaysAbsent IS NOT EMPTY
DATE AND DATETIME	Fields of these types are formatted as a date or date and time.	The date of birth is before June 30, 2010. BirthDate < 6/30/2010 The profile was created on December 12, 2013, at 1:33 PM. Profile_Created_On > "12/1/2013, 1:33 PM" The profile was created on December 12, 2013, at 1:33 PM. Profile_Created_On > "2013-12-01 13:33:000.00" (hours:minutes:seconds:millliseconds)
KEYWORD SELECTION	Fields of this type contain a selection from a pre-defined set of choices. For example, the field, Sex, contains pre-defined values of Male and Female. Although you cannot compare keyword values using greater than or less than, you can effectively use the Order keyword. For example, Grade.Order gives the index position of the Grade value in the keyword table (0..n-1). The top keyword in the keyword table corresponds to zero. So if you want to test if one grade field is greater than another grade field, you could use NextGrade.Order > Grade.Order. To access values stored in the fields of a keyword table (other than the keyword itself), use the format KeywordField.FieldName. To retrieve the keyword as a character field (for example, you want to force the display of the keyword code on a standard report), use the format KeywordField.Keyword.	The Sex field is equal to the keyword Male. Sex=Male The Sex field is equal to the keyword Female. Sex=Female The Grade field is equal to the keyword 01. Grade=01 The Grade field is not equal to the keyword 08. Grade<>08 The Grade field does not contain a keyword. Grade IS EMPTY The Grade field contains a keyword. Grade IS NOT EMPTY The grade is 03, 04 05, 06, or 07. Grade IN (03, 04, 05, 06, 07) The grade is not 03, 04 05, 06, or 07. Grade NOT IN (03, 04, 05, 06, 07)
PROFILE REFERENCE	Fields of this type contain a reference to a profile of a different type. For example, in the student profile there is a HomeSchool field that references a location profile.	HomeSchool field is equal to the location with an ID of 100010. HomeSchool=100010 HomeSchool field is not equal to the location with an ID 20020. HomeSchool <>100010 HomeSchool field is empty. HomeSchool IS EMPTY HomeSchool field is not empty. HomeSchool IS NOT EMPTY With a profile reference field, you can also use a period operator (.) to access fields in the profile. The home school is in NJ. HomeSchool.State="NJ" The location of the reading class is equal to the ID of 100010. Class[Reading].Location=100010 The last name of the teacher for the Reading class is Kelly.) Class[Reading].Teacher.LastName = "Kelly"