Create Query Expressions to narrow the results.
Query Expression Box
Note: Not all fields include Criteria. For instance, a Check Box does not have searchable Criteria and needs only an Operator.
Heading Choices Result
Available for Field Type Not Switches the operator into its opposite.
(example not =)
All
Operator Finds all contents for which the selected field is Yes, No or Empty.
= Finds records for which the selected field is equal to the specified criterion.
Number, Date, Text
> Finds all records for which the selected field is greater than the specified criterion.
Number, Date
< Finds all records for which the selected field is less than the specified criterion.
Number, Date
> = Finds all records for which the selected field is greater than or equal to the specified criterion.
Number, Date
< = Finds all records for which the selected field is less than or equal to the specified criterion.
Number, Date
Between Finds all records for which the selected field is between the specified criterion.
Number, Date
In Finds all records for which the selected field is included in the list of criteria. (example criteria; Northern, North, No. or 32, 35, 36, 39)
Number, Text
Like Allows a wildcard character (*). Finds all records for which the selected field matches the specified pattern expression. (example; *ist)
Text
Contains Finds all records for which the selected field contains a specified character sequence.
Text
Empty It is important to note that Empty is a Condition and not the same as a
Condition left blank. A Condition that is left blank will be included in the results but not in the search. A Condition that is set to Empty will only return records that
Number, Check Box, Text
contain an empty field.
True The field is a Boolean (Check Box) and is True.
Check Box
False The field is a Boolean (Check Box) and is False.
Check Box
Criterion Criteria are entered into this box. If the operator is 'Between' two boxes will display to enter both criteria.
Date, Number, Text
Sort
Allows the results to be sorted.
It is often useful to sort duplicate data so that results are easier to read and the information may be grouped on a report. For example, if a report is desired that lists Collection Objects according to their Taxon Name then the query must be created with the Taxon Name sorted and then grouped in the Report.
All
Do not sort.
Sort in descending order.
Sort in ascending order. See above.
Show Includes the field in the results. All
Prompt This will provide a 'prompt' dialog which allows the query expression to be customized each time a Report is run on the query.
All
Always
Includes the query Condition when running a Report by dropping a Record Set onto it (in the Report side bar). If 'Always' is not checked then the
All
resulting Report will include all record ID's rather than only those matching the query Condition. This is particularly useful when searching Prep Types and Current Determinations.
Delete the expression from the query. When editing a Saved query any expressions used in a report may not be deleted.
All
Using Wild Cards
The operator Like allows a search to be restricted to a specific pattern of letters or symbols or both in a designated field. The wildcard character is an asterisk (*).
*Clinton Returns any string with Clinton at the end. Clinton*
Returns any string that begins with Clinton.
*Clinton* Returns any string that includes Clinton anywhere in the string.
When it is necessary to use an asterisk (*) as part of the literal search criteria, precede it with a "\". For example; when it is desired to find Cli*nton enter the search criteria as *Cli\*nton*.
Other Controls
The order of the condition row determines the order in which fields will be sorted as well as the arrangement of the fields in the results panel. If a query includes both Taxon Name and the Last Name of the Collector and both are sorted but Taxon Name is ordered first, Specify will sort Taxon Name first, then Last Name of the Collector.
Side Buttons Function Result
Reorder Down Move the field down in order. The order determines how they will display in the results window. Reorder Up Move the field up in order. The
order determines how they will display in the results window.
Bottom Controls Result Search Synonyms
(checkbox)
Includes synonyms in the query and query results.
Distinct (checkbox)
Returns unique results only (no duplicates). When the Distinct button is used in a query, the query can not be used to create record sets, view results in data entry forms, or run reports.
When both Distinct and Sort are used in a Query the database may need to create its own Sort priorities due to the way that MySQL retrieves data.
Count (checkbox)
Gives a count of the results in a pop up dialog in the query work space only. No results will display when the Count feature is in use.
Search (button) Engages the query. Pressing the Enter key will also engage the query.
Save / Save As
(toggle button)
Click the Save button to save the query as the same name or click the (toggle) button to choose Save As and rename the query. Saved queries appear in the side bar and can be reused.