Microsoft Access SQL Keywords: NOT

	SQL Keywords: NOT

Description

The NOT keyword is used to negate an operation in SQL. It can be used to negate the NULL keyword when creating a column to indicate that the column cannot have null values.

Examples

Here is an example:

CREATE TABLE Contractors(FirstName TEXT NULL,
                         LastName VARCHAR NOT NULL);

This type of using NOT NULL is typically used when creating the primary key of a table. Here is an example:

CREATE TABLE Genders(GenderID COUNTER(1,1) PRIMARY KEY NOT NULL,
                     Gender varchar(20));

The SQL provides the NOT operator used to negate a statement. An example would be

NOT (Gender = "Female")

Consider a SQL statement used to get a list of employees from the corporate department:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Employees.Department " & _
                   "FROM Employees " & _
                   "WHERE Employees.Department = 'Corporate';"
End Sub

Imagine that, instead of getting the list of employees who work at the corporate office, you are interested in the employees who don't work at the corporate office. To get this list, you can negate the "Corporate" condition that was stated earlier. This is done by preceding the condition with NOT. This would be done as follows:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Employees.Department " & _
                   "FROM Employees " & _
                   "WHERE NOT (Employees.Department = 'Corporate');"

    txtDateHired.ControlSource = "DateHired"
    txtFirstName.ControlSource = "FirstName"
    txtLastName.ControlSource = "LastName"
    txtDepartment.ControlSource = "Department"
End Sub