Search Conditions

In the Conditions combo box on a search form you can select a certain condition to apply when searching for records. The default will always be "Like", which should be used for all normal searching. The different conditions allow you to customize what you are searching for in a more specific manor. Below are the descriptions and examples of all the different search conditions available.

Keep in mind no matter what none of the conditions will return a null or empty searched field. In other words if you set the condition for a Last Name field to <> (Not Equal) and enter 'SMITH', the search form will return every Last Name that is not SMITH but will not return any records where the Last Name is empty, blank or Null.

Example of Conditions

Condition	Example	Description
Like	Last Name Like Johnson Last Name Like John[*]n could return Johnson, Johnston, Jonathan	Searches for data based on a pattern search you enter to find data in the selected fields of the search form. See the Examples using the Like Condition below for more on this powerful searching condition.
Not Like	Last Name Not Like Johnson	Same as Like except returns the opposite of what was found.
=	Last Name equals Johnson	Searches for data Equal to the entered data.
<>	Last Name does not equal Johnson	Searches for data Not Equal To the entered data.
<	Last Names Less Than Johnson	Searches for data Less Than the entered data.
<=	Last Names Less Than Or Equal To Johnson	Searches for data Less Than Or Equal To the entered data.
>	Last Names Greater Than Johnson	Searches for data Greater Than the entered data.
>=	Last Names Greater To or Equal To Johnson	Searches for data Greater Than or Equal To the entered data.

Examples using the Like Condition (Pattern Matching)

Kind of match	Pattern	Matches Found	Matches Not Found
Multiple characters	a*a	aa, aBa, aBBBa	aBC
	ab	abc, AABB, Xab	aZb, bac
Special character	a[*]a	a*a	aaa
Multiple characters	ab*	abcdefg, abc	cab, aab
Single character	a?a	aaa, a3a, aBa	aBBBa
Single digit	a#a	a0a, a1a, a2a	aaa, a10a
Range of characters	[a-z]	f, p, j	, &
Outside a range	[!a-z]	, &, %	b, a
Not a digit	[!0-9]	A, a, &, ~	, 1, 9
Combined	a[!b-m]#	An9, az0, a99	abc, aj0

Pattern Matching

Built-in pattern matching provides a versatile tool for making string comparisons. The following table shows the wildcard characters you can use with the Like operator and the number of digits or strings they match.

Character(s) in pattern	Matches in expression
?	Any single character
*	Zero or more characters
#	Any single digit (0 - 9)
[charlist]	Any single character in charlist
[!charlist]	Any single character not in charlist

You can use a group of one or more characters (charlist) enclosed in brackets ([ ]) to match any single character in expression, and charlist can include almost any characters in the ANSI character set, including digits. In fact, you can use the special characters opening bracket ([ ), question mark (?), number sign (#), and asterisk (*) to match themselves directly only if enclosed in brackets. You can't use the closing bracket ( ]) within a group to match itself, but you can use it outside a group as an individual character.

In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range. For example, using [A-Z] in pattern results in a match if the corresponding character position in expression contains any of the uppercase letters in the range A through Z. You can include multiple ranges within the brackets without delimiting the ranges. For example, [a-zA-Z0-9] matches any alphanumeric character.

Other important rules for pattern matching:

An exclamation mark (!) at the beginning of charlist means that a match is made if any character except those in charlist are found in expression. When used outside brackets, the exclamation mark matches itself.

You can use the hyphen (-) either at the beginning (after an exclamation mark if one is used) or at the end of charlist to match itself. In any other location, the hyphen identifies a range of ANSI characters.

When you specify a range of characters, the characters must appear in ascending sort order (A-Z or 0-100). [A-Z] is a valid pattern, but [Z-A] isn't.

The character sequence [ ] is ignored; it's considered to be a zero-length string ("").