Comparative Operators

When you perform an Advanced lookup or a SQL Where Clause lookup, you can use a number of comparative operators, such as "equals," "less than," "greater than," and "starts with."

For example, the following WHERE clause, in which the comparative operator is >, meaning "greater than," searches for all projects where the project number is greater than 97000.00:

( PROJ.prProject>’97000.00’)

You use different operators with different data types: string, date, number, memo, check box, and employee.

Operator	Data Type	Expression	SQL Where
equals	string	Project Name equals "Project Red"	PR.Name = 'Project Red'
in list	string	Project Name in list "Project Red"	PR.Name = 'Project Red'
starts with	string	Project Name starts with "Project Red"	PR.Name LIKE 'Project Red%'
equals	date	Incident Date equal 08/14/06	DATEDIFF(dd,'2006-08-14', ProjectCustomTabFields.custIncidentDate) = 0
greater than	date	Incident Date greater than 08/14/06	ProjectCustomTabFields.custIncidentDate > '2006-08-14'
less than	date	Incident Date less than 08/14/06	ProjectCustomTabFields.custIncidentDate < '2006-08-14'
equals	number	Prior Years with this Firm equals 10	EM.PriorYearsFirm= 10
greater than	number	Prior Years with this Firm greater than 10	EM.PriorYearsFirm> 10
less than	number	Prior Years with this Firm less than 10	EM.PriorYearsFirm< 10
contains	memo	Test Memo contains Studio	EmployeeCustomTabFields.custtestmemo LIKE '%Studio%'
not contains	memo	Test Memo not contains Studio	EmployeeCustomTabFields.custtestmemo Not LIKE '%Studio%'
empty	memo	Test Memo empty	EmployeeCustomTabFields.custtestmemo is Null
not empty	memo	Test Memo not empty	EmployeeCustomTabFields.custtestmemo is not null
checked	checkbox	N/A	N/A
not checked	checkbox	N/A	N/A
equals	employee	N/A	N/A
in list	employee	N/A	N/A
starts with	employee	N/A	N/A