Comparison operators (ADP)

Microsoft Office Access 2003

previous page next page

You can use any standard SQL operators in a search condition. When you use operators in a search condition, the following rules apply:

  • The data types of the data used in the comparison must match. That is, only text can be compared to text, numbers to numbers, and so on. You can use a function or keyword (such as CAST) to temporarily convert data to another data type.
  • If you compare text data, the result depends on the character set currently in use. For example, if a table was created using Scandinavian characters, the search results might differ depending on whether your current character set (code page) is Scandinavian or another character set.
  • If a comparison value is null, the result is unknown. Nulls are not matched to any value, including other instances of null. For example, if you are searching for a name beginning with the letter "M" or higher (name >= 'M'), and some of the rows contain no value, those rows do not appear, no matter what comparison operator you use.

ShowSummary of search condition operators

The following table summarizes search condition operators that are defined for standard SQL.

Operator Meaning Grid pane example SQL pane example
= Equal. 
= 'Smith'
							
SELECT fname, lname
FROM employees
WHERE lname = 'Smith'
<>
!=		 Not equal to. 
<> 'Active'
							
SELECT fname, lname
FROM employees
WHERE status <> 'Active'
> Greater than. 
> '01 Jan 1995'2
							
SELECT fname, lname
FROM employees
WHERE hire_date >
 '12/31/90'
< Less than. 
< 100
							
SELECT fname, lname
FROM employees
WHERE job_lvl < 100
>=
!<		 Greater than or equal to. 
>= 'T'
							
SELECT au_lname
FROM authors
WHERE au_lname >= 'T'
<=
!>		 Less than or equal to. 
<= '01 Jan 1995' 2
							
SELECT fname, lname
FROM employees
WHERE hire_date <= 
 '01/01/95'
BETWEEN expr1
AND expr2		 Tests range of values. 
BETWEEN 
'01 Jan 1995' 
AND 
'31 Dec 1995'2
							
SELECT fname, lname
FROM employees
WHERE hire_date 
 BETWEEN '12/31/90'
 AND '12/31/91'
IS [NOT] NULL Tests whether contents of column or result of expression is null. 
IS NULL
							
SELECT fname, lname
FROM employees
WHERE photo_on_file IS NULL
[NOT] LIKE Performs pattern matching (usually restricted to character data types). 
LIKE ('MAC%')
							
SELECT fname, lname
FROM employees
WHERE lname LIKE ('MAC%')
expr1 [NOT] IN
(val1, val2, …)

– or –

expr1 [NOT] IN
(subquery)		 Matches list of specific values by testing whether expr1 appears either in a list of values or in the result set of a subquery. 
IN ('SW', 'SE')

supplier_id IN
  (subquery)

							
SELECT fname, lname
FROM employees
WHERE sales_region IN ('SW', 'SE')

SELECT product_nameFROM products
WHERE supplier_id IN
  (SELECT supplier_id
  FROM supplier
  WHERE (country = 'Sweden'))
ANY (SOME) Tests whether one or more rows in the result set of a subquery meet the specified condition. (ANY and SOME are synonyms; the Query Designer will use ANY when creating an SQL statement.) 
<> ANY (subquery)
							
SELECT au_lname, au_fname
FROM authors
where city <> any
 (SELECT city FROM publishers)
ALL Tests whether all rows in the result set of a subquery meet the specified condition. 
advance > ALL (subquery)
							
SELECT title FROM titles
where advance > all
  (SELECT advance FROM
   publishers,titles
  where titles.pub_id 
   = publishers.pub_id
  AND pub_name = 
   'Alogdata Infosystems')
[NOT] EXISTS Tests whether a subquery returns any results whatsoever (not a specific result). 
EXISTS (subquery)
							
SELECT product_name
FROM products
WHERE EXISTS
  (SELECT * FROM 
   orders, products
  WHERE orders.prod_id 
  = products.prod_id)


previous page start next page