Search with a Select Statement

You can find data in any field with a Select Statement, including those unavailable in the Search Fields section of Advanced Search.

Steps

Click  Advanced on the toolbar to open Advanced Search.
Choose Select Statement as the search method.
Click into the Select Statement field, enter your Select Statement, and press Enter. See information about format, components, and examples further down on this page.
Click Next and then click Finish to see your results.

Video

Select Statement format

Select Statements have up to 5 parts.

Example: SELECT OBJECT WITH PROD_PRI_PERSON = ”Jane Smith”

Part	Example	Notes
File to select	SELECT OBJECT	You don’t need to enter this part. It’s automatically entered by the system when you press Enter after typing the other parts of the statement.
Keyword	WITH	Other keywords are listed below.
Internal field name	PROD_PRI_PERSON	For more information, see Get an internal field name or Field Mark Count (FMC). You can also click  Options on the toolbar to select from a list of internal field names.
Comparison operator	=	Other comparison operators are listed below.
Data to search for	"Jane Smith"

Select Statement components

Keywords

Keyword	Function
WITH	Finds records containing the specified data.
WITHOUT	Finds records that don’t contain the specified data.
CASESENS	Forces a case-sensitive search of the specified data.

Comparison operators

Comparison operator	Function	Notes
=	Equal to
<	Less than
<>	Not equal to
>	Greater than
]	Starts with
[	Ends with
[]	Containing	You can write CONTAINING instead of [].

Example 1: Search for a presence or absence of data

Find objects with data in a field

SELECT OBJECT WITH DEPARTMENT

This statement will find Object records that contain data in the Department field.

Find objects without data in a field

SELECT OBJECT WITHOUT DEPARTMENT

This statement will find Object records that don’t contain data in the Department field.

Example 2: Search for data within a field

Find objects that contain a word

SELECT OBJECT WITH MATERIAL_DESC [] "SILVER"

or:

SELECT OBJECT WITH MATERIAL_DESC CONTAINING "SILVER"

Either of these statements will find Object records that contain the word silver in the Medium/Material Description field.

Find objects that contain a phrase in a text field

SELECT OBJECT WITH BRIEF_DESC [] "WOODEN FIGURE"

or:

SELECT OBJECT WITH BRIEF_DESC CONTAINING "WOODEN FIGURE"

Either of these statements will find Object records that contain the phrase wooden figure in the Brief Description field, and will exclude records that simply contain both the words wooden and figure.

Example 3: Search on date fields

Find objects classified between certain dates

SELECT OBJECT WITH CLASSIFICATION_DATE FROM "01 Jun 2015" TO "30 Jun 2015"

This statement will find Object records where the Classification Date is between 1 and 30 June 2015.

Find objects with a current location date

SELECT OBJECT WITH CURR_LOC_DATE = "6/12/21"

This statement will find Object records with a Current Location Date of 6/12/21.

Find objects with an earlier current location date

SELECT OBJECT WITH CURR_LOC_DATE < "6/12/21"

This statement will find Object records with a Current Location Date earlier than 6/12/21.

Find objects with any current location date other than that specified

SELECT OBJECT WITH CURR_LOC_DATE <> "6/12/21"

This statement will find Object records with any Current Location Date other than 6/12/21.

Example 4: Search with case sensitivity

Find data with any case (sentence case, lower case, and all caps)

SELECT PERSON WITH LAST_NAME = "Smith"

This statement will find Person records with Smith, SMITH or smith in the Last Name field.

Find data using a specific case (for example, sentence case)

SELECT PERSON WITH CASESENS LAST_NAME = "Smith"

This statement will find Person records with Smith in the Last Name field. SMITH and smith will not be included.