Examples that best illustrate some of the more powerful and unique features are identified by a 
on the left. Also, please pay special attention to the comments in bold since they generally give information that may not be obvious.
Although the rules are actually very intuitive and easy to grasp, an initial reading of them may make the language seem much more complex than it really is. In this regard, if you have not already done so, it is suggested that you first read the Quick-Guide to the language. This will get you acquainted with the basic language features and provide a context for learning the more advanced ones.
If you enter no criteria into a field's text-box, that field will be ignored in the selection process, i.e. no record will be rejected because of any value that might be present in that field. On the other hand, if you do enter some criteria into a field's text-box, any record where that field does not meet the specified criteria will be rejected. Thus, for a record to be accepted and listed in the response, all the non-blank criteria must be satisfied.
Examples that best illustrate some of the more powerful and unique features are identified by a on the left. Also, please pay special attention to the comments in bold since they generally give information that may not be obvious.
| * | Text-box Entry | Explanation |
|---|---|---|
| IS Samson | checks for an exact match of the entire field value with the specified criteria value, Samson. Note that all comparison matches, including this one, are by default case-sensitive - although that can be overridden by use of UPPER (see later). Note also that all special command words (IS in this example) must be in all uppercase. The use of a lowercase "is" in this example will cause an error. | |
| IS "Samson" | double-quotes can always be used around any value. Double-quotes are not required in this case, however, since there are no blanks or non-alphabetic characters in the criteria value | |
| IS "Van Roost" | double-quotes are required because of the embedded blank in the criteria value. It would also be required if any non-alphabetic character appeared in the criteria value. | |
| BEGINS Marg | checks for any field values beginning with "Marg". Might be used, for example, to match with field values starting with either "Margret" or "Marguerite", etc. |
| ENDS son | checks for any field values ending with "son" |
| CONTAINS Joseph | checks for any field values containing the string "Joseph" anywhere. If used in a Given-Names field, for example, this criteria would match field values of "John Joseph", "Joseph", "Henry Joseph Elias", "Joseph George", etc. |
| Joseph | any omitted comparison-verb is assumed to be CONTAINS. Thus, this criteria is equivalent to the example immediately above. |
| UPPER IS LeBlanc | the modifier UPPER forces the associated comparison(s) to be made using uppercase versions of both the field value and criteria value. This is most useful when one is unsure how the field value might be capitalized (e.g. Leblanc or LeBlanc). |
| IS Samson OR IS Sampson | the logical-connectors AND and OR can be used to form criteria. Parentheses are not required, except when necessary to remove any ambiguity concerning the grouping of the individual criteria units. | |
| IS (Samson OR Sampson) | criteria values governed by identical comparison-verbs may optionally be grouped by use of parentheses. This is equivalent to the example immediately above. |
| CONTAINS Joseph AND (CONTAINS Jean OR CONTAINS John) | parentheses are required here in order to resolve ambiguity. | |
| Joseph AND (Jean OR John) | This is equivalent to the example immediately above. It takes advantage of the feature that all omitted comparison-verbs are taken to be CONTAINS. |
| IS NOT John | the use of NOT reverses the meaning of the criteria-unit with which it is associated. If the NOT is associated with a unit consisting of a single comparision-verb, as it is here, it may optionally preceed that verb rather than follow it. Thus, "NOT IS John" is completely equivalent to this example. | |
| Some Additional Useful Examples | ||
| Joseph AND NOT (Jean OR John) | selects records where the field value contains the string "Joseph", but does not contain either "Jean" or "John". If fully expanded, this would become "CONTAINS Joseph AND NOT (CONTAINS Jean OR CONTAINS John)". | |
| >= "1870-01-01" AND < "1870-07-01" | assuming that the criteria is associated with a character-date field with the format YYYY-MM-DD, this selects records with dates in the range from 1 Jan 1870 to before 1 Jul 1870. Note that all comparison-verbs ( >= and < in this example) must in general be separated by spaces from the other criteria elements. A left parenthesis (or the beginning of the line), however, is also acceptable in place of the left space. | |
The basic unit used in stating the criteria for an individual field is a comparison, which is simply the combination of a comparison-verb with a value. The legitimate comparison-verbs are: IS , CONTAINS , BEGINS , ENDS, = , > , >= , < , <= . The value can be any string of characters (in some databases, also arithmetic numbers)that might occur in that field in the database.
The usage of many of the comparison-verbs is the obvious one, but some explanation may be in order:
Comparison values may be any string of characters that do not contain any of the following: percent-sign (%), underscore (_), pound-sign (#), double-quote ("), or vertical-bar (|). If the value contains a non-alphanumeric character (e.g. +, -, ?) or a space, it must be enclosed between a pair of double-quotes. It is always acceptable to enclose a value within double-quotes, however, even when this is not necessary.
The logical connectors "AND" and "OR" are used to join simple comparisons into more complex comparisons. The operator "NOT" reverses the result of a comparison.
Parentheses may always be placed around simple comparisons (e.g. "(IS Samson)"), although the are not necessary in those cases. The may also be placed around compound comparisions that are made up of simple comparisons that are joined by AND and OR conectors. The presence of a set of parentheses around comparisons signals that the entire group of the enclosed comparisons should itself be treated as a simple comparison by the elements outside of the parentheses.
As mentioned above, parentheses are often necessary to avoid ambiguity whenever one or more of the components of a compound comparision is itself a compound comparision (e.g. "(BEGINS John OR BEGINS Jean) AND CONTAINS Pierre"). This also is true when the NOT operator is applied to a compound comparision (e.g. "NOT (IS Jean OR IS John)".
There is another use of parentheses that often may save some verbiage. One may use parentheses to "factor out" an identical comparison verb and/or modifier from a sequence of comparisons. For example, "UPPER IS Samson OR UPPER IS Sampson OR UPPER IS Sancon" could be written as "UPPER IS (Samson OR Sampson OR Sancon)"
While this offers considerable flexibility in the formulation of queries, there are many useful queries that cannot be posed within these constraints. Often, multiple queries are required. A good example is the case where one is seeking all records that have "Samson" in either its Father-Surname field or in its Godfather-Surname field. If one simply entered these criteria in the Father-Surname and Godfather-Surname criteria boxes, DBQuery would treat them as being joined by an AND - and not as the OR that is desired.
A way around this would be to run two separate queries and then manually "meld" the two response sets, eliminating whatever duplicates that may occur. DBQuery offers the facility to accomplish this by supporting multiple queries on the same form and automatically merging the response sets.
It is often useful to run several related queries and then merge the response sets to eliminate duplicate records. To accomplish this, one must enter the criteria associated with each field for all of the queries into that field's criteria-box - separating the individual queries with the "|" character.
The example of the two queries 1) "Father-Surname IS Samson" 2) "Godfather-Surname IS Samson" would be written:
Note that the trailing "|" could have been omitted from the Father-Surname criteria.
Since the Query Separators show that this is two distinct queries, each will be run separately and their response sets then merged - with the duplicates eliminated. The result be be all records with either the Father's or Godfather's Surname as Samson - with those records where both the Father and Godfather's surnames were Samson appearing only once.