Lesson 25: Intermediate Data Selections

	Intermediate Data Selections

Logical Operators

Negation Operator: NOT

So far, when addressing a condition, we assumed that it was true. The SQL provides an operator that, considering a given condition, would be negated. In other words, this operator finds the contrary of the stated condition. This operator is called NOT. 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

This would produce:

NOT

The IS Operator

To validate something as being possible, you can use the IS operator. For example, to acknowledge that something is NULL, you can use the IS NULL expression. To get a list of employees whose department is not specified, you would use a statement as follows:

SELECT LastName, FirstName, HourlySalary
FROM   Employees
WHERE  Department IS NULL

In the same way, to validate that something is not null, you can use the expression IS NOT NULL. To see a list of only the employees whose records indicate the department, you can use a statement as follows:

SELECT LastName, FirstName, HourlySalary
FROM   Employees
WHERE  Department IS NOT NULL

Pattern Operator: LIKE

Introduction

Most or all of the criteria we have specified with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact desired value of records but you want to specify some type of approximation. To do this, you use the LIKE operator.

If you are visually creating the statement, in the Select Query window, click the Criteria box corresponding to the column on which the condition would be applied and type. In a SQL statement, the LIKE operator is used in a formula as follows:

Expression LIKE pattern

The Expression factor is the expression that will be evaluated. This must be a clear and valid expression.

The pattern factor can be a value to be found in Expression. For example, it can be the same type of value used in a WHERE statement. In this case, the equal operator would be the same as LIKE. For example

SELECT Employees.DateHired, Employees.FirstName,
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName)="Scott"));

is equivalent to

SELECT Employees.DateHired, Employees.FirstName, 
       Employees.LastName, Employees.Department
FROM Employees
WHERE (((Employees.LastName) Like "Scott"));

The idea of using a LIKE operator is to give an approximation of the type of result you want. The available wildcards to se with the LIKE operator are:

LIKE Any Character *

If you want to match any character, in any combination, for any length, use the * wildcard. If you precede it with a letter, as in S*, the condition would consist of finding any string that starts with S. Imagine that you want to create a list of employees whose last names start with S. You would type the condition as LIKE "S*" (or LIKE 'S*'). To do this visually, in the Criteria field of the column, type the condition. Here is an example

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

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

This would produce:

To negate the condition, you can precede the criterion with NOT. That is type the NOT operator just after WHERE. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Employees.Department " & _
                   "FROM Employees " & _
                   "WHERE Not (Employees.LastName Like 'S*');"

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

This would produce:

As you can see, this results in the list of employees whose last names don't start with S.

In Microsoft Access, you can type the NOT operator before the LIKE expression. If the name of the column involved in the criterion is qualified, you must include it in parentheses. Here is an example:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Employees.Department " & _
                   "FROM Employees " & _
                   "WHERE (Employees.LastName) Not Like 'S*';"

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

When you precede the * character with a letter, only that letter would be considered. Alternatively, you can specify a group of characters that would precede the * symbol. For example, if you have some last names that start with San in a list but you don't remember the end of the name you are looking for, to create the list, you can specify that the first name would start with San and end with whatever. In this case, you would use San* as follows:

The corresponding SQL statement is:

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

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

This would produce:

Instead of ending a letter or a group of letters with *, you can begin the LIKE statement with *. An example would be LIKE "*son". In this case, all strings that end with son, such as Johnson or Colson, would be considered.

If you remember neither the beginning nor the end of a string you want to search for but you know a sub-string that is probably included in the type of string you are looking for, you can precede it with * and end it with *. An example would be LIKE "*er*". In this case, all strings that include er anywhere inside, such as Berg or Merck, would be considered.

Like the other SQL statements, you can also negate this one.

Practical Learning: Selecting Records LIKE

Open the ROSH1 database from the previous lesson
In the Navigation Pane, right-click SelectStudents and click Design View

Add new controls to the Form Footer section as follows:

Control	Name	Caption
Text Box	txtLastName	Last Name Like:
Button	cmdFindByLastName	Find

Right-click the Find button and click Build Event...
In the Choose Builder dialog box, double-click Code Builder

Implement the event as follows:

Private Sub cmdFindByLastName_Click()
    If IsNull(txtLastName) Then
        RecordSource = "SELECT * FROM Students"
    Else
        RecordSource = "SELECT * FROM Students " & _
                       "WHERE LastName LIKE '" & txtLastName & "*';"
    End If
End Sub

Return to Microsoft Access
Switch the form to Form View
In the bottom text box, type ba
Click the Find button
Close the form
When asked whether you want to save it, click Yes

LIKE a Range of Characters []

The * wildcard is used to precede or succeed a specific character or a group of characters, that is, any character. If you want to consider only a range of characters from the alphabet, you can include the range in square brackets. To do this, type [, followed by the lowest character of the range, followed by -, followed by the highest character of the range, followed by ]. For example, to get a list of employees whose last names start with letters between E and H, you would specify the criterion as LIKE "[E-H]*". Here is an example:

The SQL statement of this query is:

Private Sub cmdDataSource_Click()
    RecordSource = "SELECT Employees.DateHired, " & _
                   "       Employees.FirstName, " & _
                   "       Employees.LastName, " & _
                   "       Employees.Department " & _
                   "FROM Employees " & _
                   "WHERE Employees.LastName Like '[E-H]*';"

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

This would produce:

Once again, remember that you can negate this expression by preceding it with NOT.

LIKE an Unknown Character ?

Imagine that you know a certain pattern in the string but you are forgetting only one letter. For example, if you know that the employee's first name sounds like Saul, Haul, or Paul. In other words, you don't know for sure what the first letter is but you are sure about the other characters. To match a character in a string, you can use the ? symbol as the wild card. Here is an example:

SELECT 	Employees.DateHired, Employees.FirstName,
	Employees.LastName, Employees.Department
FROM Employees
WHERE Employees.FirstName Like "?aul");

Logical Conjunction

Introduction

In the previous sections lessons, we stated the conditions one at a time. This made their interpretation easier. Sometimes, you will need to test a condition that depends on another. Boolean algebra allows you to combine two conditions and use the result, or to test two conditions but consider if either is true.

Suppose you get an assignment to create a database used for a company that rents apartments to customers. The operations would consist of registering customers who become tenants and allocating them an apartment based on their needs and the availability.

Practical Learning: Introducing Conjunctions

Start Microsoft Access
From the resources that accompany these lessons, open the Solas Property Rental1 database

Introduction to Logical Conjunctions

For a property rental company, if a customer who is a husband and father of one comes to the rental office and states that he wants a 2-bedroom property, you would check the listing of the properties and find out that you have quite a few of them. To respond to this request, you must examine two conditions for each apartment:

The property has two bedrooms
The property is available

When preparing your database prior to seeing the customers, you can start by building one query that lists only the properties that have two bedrooms:

Solas Property Rental

The second condition requires that the property be available. From our database, a property is available if its OccupiedVacant field is set to Vacant:

From these two results, notice that there is no relationship between the fact that a property has 2 bedrooms and its being vacant. To rent a property for our customer, it must have two bedrooms. We can create a Boolean truth table as follows:

The Property has 2 Bedrooms	The Property is Vacant	Result
True
False

The property to rent must have two bedrooms. This excludes the 1, 3, and more-bedroom properties. This means that if the property has a number of bedrooms other than 2, whether it is available or not, it cannot be rented to the current customer:

The Property has 2 Bedrooms	The Property is Vacant	Result
True		Don't Know
False	Regardless	False

Once we have a list of properties that have two bedrooms, now, let's consider the available properties. If a property has two bedrooms and it is available, then it can be rented to the current customer:

The Property has 2 Bedrooms	The Property is Vacant	Result
True	True	True
False	Regardless	False

If the property has 1, 3 or more bedrooms but not 2, whether it is available or not, it cannot be rented to the current customer:

The Property has 2 Bedrooms	The Property is Vacant	Result
True	True	True
Regardless	False	False
False	Regardless	False

In the same way, if both conditions are false (the property has 1 or more than 2 bedrooms and in fact it is not available), the result is false (the property cannot be rented to the current customer):

The Property has 2 Bedrooms	The Property is Vacant	Result
True	True	True
Regardless	False	False
False	False	False
False	Regardless	False

This demonstrates that a property can be rented to the current customer only if BOTH conditions are met: The property has two bedrooms AND it is available. This type of condition is referred to as logical conjunction.

Logical Conjunction in Tables, Queries, and Forms

As mentioned in previous sections, before performing data analysis, first display the table or query in Datasheet View, or the form in Form View. After displaying the object, on the Ribbon, click Home. In the Sort & Filter section, click Advanced -> Filter By Form. To perform logical conjunction, select the values of two (or more) columns in the same row. After setting the criteria, you can click the Filter button on the Ribbon to see the result.

After viewing the result, to restore the table, query, or form, on the Ribbon, you can click Toggle Filter.

When you perform filtering on a form, it fires the On Filter event. When you apply the filter on a form, it fires an On Apply Filter event.

Practical Learning: Applying the AND Operator in a Form

In the Forms section of the Navigation, right-click Properties and click Copy
Right-click somewhere in the Navigation Pane and click Paste
Type AvailableApartments as the name of the copied form and click OK
Right-click the AvailableApartments form and click Design View:
Access the form's Properties window and click Event
In the Event tab, double-click On Open and click its ellipsis button

Implement the event as follows:

Private Sub Form_Open(Cancel As Integer)
    Filter = "(PropertyTypeID = 1) AND (OccupiedVacant = 'Vacant')"
    FilterOn = True
End Sub

Return to Microsoft Access and switch the form to Form View to preview it
Close the form
When asked whether you want to save it, click Yes

Logical Conjunction in SQL Statements

To express the logical conjunction, the SQL uses the AND operator. To use it visually when creating a query, after selecting the columns, in the lower section of the window, click the Criteria box corresponding to each column that will be involved in the conjunction. For example, if you want to create a list of movies released in 1994 but rated R, type the appropriate value in the Criteria boxes of the columns. Here is an example:

Videos

To manually create a logical conjunction in SQL, type one condition on the left and the other condition on the right sides of the AND keyword using the following formula:

SELECT WhatColumn(s)
FROM WhatObject
WHERE Condition1 AND Condition2

The WhatColumn(s) and the WhatObject factors are the same we have used so far. The AND keyword is the new one. Each condition is written as a SQL operation using the formula:

Column operator Value

In this case, the WHERE operator resembles the If conditional statement. The Condition1 is the first that would be examined. Remember that, from our discussion earlier, if the first condition is false, the whole statement is false and there is no reason to examine the second condition. If the first condition is true, then the second condition would be examined. Based on this, the SQL statement used to get a list of movies released in 1994 but rated PG-13 is:

SELECT Title, Director, CopyrightYear, Rating
FROM Videos
WHERE CopyrightYear = "1994" AND Rating = "PG-13";

The equivalent SQL statement of the above query in SQL as written by Microsoft Access is:

SELECT Videos.Title, Videos.Director, Videos.CopyrightYear, Videos.Rating
FROM Videos
WHERE (Videos.CopyrightYear)="1994") AND ((Videos.Rating)="PG-13");

Practical Learning: Applying the AND Operator in a Query

On the Ribbon, click Create
In the Forms section, click More Forms -> Form Wizard
In the Tables/Queries combo box of the first page of the wizard, select Tables: Properties
In the Available Fields, double-click PropertyNumber, Address, Locality, Bathrooms, and RentalRate
Click Next
Click the Tabular radio button
Click Next
Click Equity
Click Next
Set the Name to Available 1-Bedroom Properties
Click Modify the Form's Design
Click Finish
Double-click the button at the intersection of the rulers
In the Properties window, click the All tab
Click Record Source and press Delete
Scroll down and double-click On Open
Click its ellipsis button

Implement the event as follows:

Private Sub Form_Open(Cancel As Integer)
    RecordSource = "SELECT PropertyNumber, Address, " & _
                   "       Locality, Bathrooms, RentalRate " & _
                   "FROM Properties " & _
                   "WHERE (Bedrooms = 1) AND (OccupiedVacant = 'Vacant')"
End Sub

Return to Microsoft Access and adjust the design of the form as you see fit
Switch the for to Form View
Close the form
When asked whether you want to save it, click Yes

Logical Disjunction: The OR Operator

Introduction

Suppose a customer who is shopping, but is not ready, for a rental property comes to the office and states that she is considering renting. You show her the properties with apartments, townhouses, and single-family homes. At first glance, the customer says she cannot rent an apartment. The other two options are the townhouse or the single family. To prepare the new list, you must create a query that considers only these two options. Before building the query, you can state the following:

The property is a townhouse
The property is a single-family

We can start a truth table as follows:

The Property is a Townhouse	The Property is a Single Family	Result

To continue with this table, we can check each property. If the property is a townhouse, it is considered valid for our customer:

The Property is a Townhouse	The Property is a Single Family	Result
True	Regardless	True

It a property is not a townhouse. Then, we consider the next property. If the next property is a single family, it is also valid:

The Property is a Townhouse	The Property is a Single Family	Result
True	Regardless	True
Regardless	True	True

When building this table, we would skip a property only if it is neither a townhouse nor a single family. In Boolean algebra, this means that if both conditions are true, the whole statement is also true. The whole statement is false only if both conditions are false. This can be resumed as follows:

Condition 1	Condition 2	Result
True	True	True
True	False	True
False	True	True
False	False	False

This type of statement is referred to as logical disjunction. The logical disjunction is expressed in Microsoft Access and in SQL with the OR operator.

Logical Disjunction in Tables, Queries, and Forms

To perform an OR analysis on a table or query, display it in Datasheet View or display the form in Form View. On the Ribbon, click Home. In the Sort & Filter section, click Advanced -> Filter By Form:

While in the Look For tab, click the box under the column that would be used to set the first condition, and select the desired value. After selecting the value of the first condition, click the Or tab in the lower left section of the window. Click the arrow of the combo box under the column that would be used as the second condition. After setting the criteria, to apply the filter, in the Sort & Filter section of the ribbon, click Advanced -> Apply Filter/Sort.

Once again, if you perform data filtering on a form, it fires an On Filter event.

Logical Disjunction in SQL Statements

When creating a query in Design View, the window provides two convenient sections for the first and the second conditions. To set the first condition, click the Criteria box corresponding to its column and type the operation. To set the second condition, click the Or box corresponding to its column and enter the necessary condition. Here is an example:

Videos

Other Logical Operators on Queries

Selecting Values BETWEEN

If you have a logical range of values and you want to know if a certain value is contained in that range, you can use the BETWEEN operator. The BETWEEN operator is combined with AND to get a list of records between two values. The basic formula of this operator is:

Expression BETWEEN Start AND End

The Expression placeholder of our formula is usually the name of the column whose values you want to examine. The Start factor is the starting value of the range to consider. The End factor is the highest value to consider in the range. After this condition is executed, it produces the list of values between Start and End. Here is an example:

SELECT Videos.Title, Videos.Director, Videos.CopyrightYear, Videos.Rating
FROM Videos
WHERE (Videos.CopyrightYear) Between 1994 And 2004;

IN a Series of Values

If you have a series of records and want to find a record or a group of records among them, you can use the IN operator. The IN operator is a type of various OR operators. It follows this formula:

IN(Expression1, Expression2, Expression_n)

Each Expression factor can be one of the values of a column. This is equivalent to Expression1 OR Expression2 OR Expression3, etc. Here is an example that shows the list of movies directed by either Oliver Stone or Ron Howard:

SELECT Videos.Title, Videos.Director, Videos.CopyrightYear, Videos.Rating
FROM Videos
WHERE (Videos.Director) In ("Oliver Stone","Ron Howard");

Parameterized Queries

Introduction

In the previous sections, every time we created a query, we selected the columns we wanted because we knew what fields would be considered in the result. This also meant that we decided (imposed) what would be displayed to the user. In some cases, you may want to let the user specify one category (or more than one category) of values that would show in the result instead of displaying all of the items. Instead of imposing the value to the user, you can create a query that would prompt the user for a value and it would show only the records that are based on the user's choice. This is the basis of a parameter query.

A parameter query is one that requests a value from the user and displays its result based on the user's choice. As its name implies, this query expects a parameter, like the arguments we reviewed for procedures. This means that, when creating such a query, you must prepare to display a request to the user.

Creating a Parameter Query

You visually start a parameter query like any other query, by selecting the necessary columns. In the Criteria box corresponding to the column on which the choice would be based, you can enter a phrase between an opening square bracket and a closing square bracket.

When creating a parameterized query, you can use the BETWEEN, LIKE, NOT, or IN operators. For example, to let the user enter part of a name of a director, you could set the Criteria of the Director to:

LIKE "*" & [A director name that includes] & "*"

When the query runs, if the user enters a name such as Phillip, the list would include the 6th and the 12th videos. Instead of requesting just one value as a parameter, you can request more than one. To do this, you can use the BETWEEN operator that requests an additional AND. For example, to ask the user to specify a range of years whose videos you want to see, you would set the Criteria of a CopyrightYear to

BETWEEN [Enter a starting year] AND [Enter an ending year]

Practical Learning: Introducing Parameterized Queries

From the resources that accompany these lessons, open the ROSH database
In the Navigation Pane, right-click the FindStudent form and click Design View
In the Properties window, click Event and double-click On Open
Click its ellipsis button

Implement the event as follows:

Private Sub Form_Open(Cancel As Integer)
    RecordSource = "SELECT * FROM Students " & _
                   "WHERE StudentNumber = [Enter Student Number and Click OK]"
End Sub

Return to Microsoft Access
Switch the form to Form View
When asked to provide a student number, enter MR-3280-K
Press Enter
Close the form
When asked whether you want to save it, click Yes

Queries and Built-In Functions

Introduction

In previous lessons and section, we saw that the Visual Basic language was equipped with an impressive library of functions. These functions can also be used in queries and even included in SQL statements. The SQL interpreter of Microsoft Access can recognize these functions as long as you use them appropriately.

Using Functions in Queries

There are two primary ways you can include a function in a query. If you are visually building the query in the Query window, you can write an expression that includes the function. Consider the following list of employees:

Employees

Imagine that you want to create a column in a query and that column should hold the full name of each employee. In a column of a table, you could use an expression such as:

Employee: [FirstName] & " " & [MiddleName] & " " & [LastName]

Employees

The equivalent SQL statement is:

SELECT Employees.DateHired,
       [FirstName] & " " & [MiddleName] & " " & [LastName] AS Employee
FROM   Employees;

This would produce:

Employees

Notice that some employees don't have a middle name in their record and the field includes an extra useless empty space. Imagine that you only want to include a middle initial instead of the whole middle name. You can use the Left$ function to retrieve the first character of the middle name and include the call to that function in your query. To do this visually, if you are creating the query in the Query window, simply enter the function call where the column name would be. For our scenario, column name would be set as follows:

SELECT Employees.DateHired,
       [FirstName] & " " & Left([MiddleName],1) & " " & [LastName] AS Employee
FROM   Employees;

This would produce:

Employees

Once again notice that some records don't have a middle initial because they don't have a name. For the records that don't display a middle name, we can write a conditional statement, using the IIf() function, to check it and taking the appropriate action accordingly. Here is the result:

SELECT Employees.DateHired,
       IIf(IsNull([MiddleName]),
           [FirstName] & " " & [LastName],[FirstName] & " " & 
	   UCase(Left([MiddleName],1)) & " " & [LastName]) AS Employee
FROM Employees;

This would produce:

Employees

In the same way, you can use any of the built-in functions we reviewed in previous lessons.

Summary Queries and Aggregate Functions

Introduction

Besides the various functions built-in the Visual Basic language, the SQL provides an additional collection of functions that you can use to perform various valuable operations in what are referred to as summary queries. A summary query is used to perform a common operation on the values held by a query.

If you have re-occurring pieces of information in various columns of a table, you can create a query that shows them in group.

To visually create a summary query, start the query in the Query window and select the desired table(s). In the Design tab of the Ribbon, in the Show/Hide section, click the Totals button:

Summary Query

To support summary queries, you can create a GROUP BY statement. To do this, at the end of the SQL statement, precede the name of the column with the GROUP BY expression. Here is an example:

SELECT Videos.CopyrightYear
FROM Videos
GROUP BY Videos.CopyrightYear;

Grouping Values

To actually perform the necessary operation(s), a query uses a series of functions referred to as aggregate. If you are working visually, after selecting the bas column, use the other columns to set a criterion or the criteria. To programmatically set a condition in a summary query, you precede the condition with the HAVING operator. Here is an example:

SELECT  Videos.CopyrightYear, Count(Videos.CopyrightYear) AS CountOfCopyrightYear
FROM    Videos
GROUP   BY Videos.CopyrightYear
HAVING (Videos.CopyrightYear) Is Not Null;

Aggregate Functions

To perform its various operations, a summary query relies in what are referred to as aggregate functions:

In reality, a summary query uses some of the functions that ship with Microsoft Access:

Count: Microsoft Access uses the Count() function to count the number of occurrences of the category in the column and produces the total
First: Microsoft Access uses the First() function to get the first occurrence of the value in the category
Last: Microsoft Access uses the Last() function to get the last occurrence of the value in the category
If the column holds numeric values:
- Sum: The Sum() function is used to sum up the values in the category
- Avg: The sum of value in a category would be divided by the number of occurrences in that category to get the average
- Min: The lowest value of the category would be produced from the Min() function
- Max: The highest value of the category would be produced using the Max() function
- StdDev: The standard deviation of the values of the category would be calculated using the StdDev() function
- Var: The statistical variance of the values in the category would be calculated

If none of these functions is suited for the type of statistic you want to get, you can write your own expression or condition. To do this, select the Expression or the Where item. Then, in the Criteria box of the column, type the desired expression. If you select the Where option, type a valid Boolean expression that can evaluate to true or false.