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, examples, and components 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 at the bottom of this page.
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 at the bottom of this page.
Data to search for	"Jane Smith"

Searching for a presence or absence of data

Find objects with data in a field

SELECT OBJECT WITH DEPARTMENT

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

Find objects without data in a field

SELECT OBJECT WITHOUT DEPARTMENT

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

Searching on dates

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.

Searching 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.

Select Statement components

Keywords

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

Comparison operators

Comparison operator	Function
=	Equal to
<	Less than
<>	Not equal to
>	Greater than
]	Starting with
[	Ending with
[]	Containing match