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.
Condition |
Example |
Description |
Like |
Last Name Like Johnson |
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. |
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 |
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.
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 ("").