Introduction to Conditions
Practical Learning: Introducing Data Filters
Setting Criteria in a Regular View
To set the condition by which to isolate records, if a table, a query, or a form is displaying in the Datasheet View, a form in Form View, or a report in Report View or in Layout View, in the Sort & Filter section of the Ribbon (Home tab), click the Advanced button and click Advanced Filter/Sort... A window that resembles the Design View of a query will display.
Setting Criteria on the Table, Form, and Report in Design View
To prepare the condition by which some of the records must display on a table, a form, or a report in Design View, access its Property Sheet. Click Filter and type an appropriate expression.
By default, an expression set as the filter will not apply immediately. If you want the condition to apply when the object displays in its regular view, set the Filter On Load property to Yes. To programmatically set the condition for a form or a report, access its FilterOn property.
Setting Criteria on a Field
Normally, the condition for data anlasysis is set on a data field and that condition applies to the other records. This type of condition is specified in the Design View of a query or a similar window.
In the Design View, the row that controls the filters is named Criteria. To set the condition in the Design View of a query, locate the text box at the intersection of the column and the Criteria row:
WHERE is the Filter?
To assist you in creating a condition to select or exclude some records, the SQL provides the WHERE keyword. Its section, also called a WHERE clause, must come at the end of a SELECT statement. The formula to follow is:
SELECT what-field(s) FROM what-bject WHERE expression;
Remember that, to make your code easy to read, you can write the sections of the SELECT expression of different lines. In this case and by tradition, the WHERE clause is usually written on its own line (rememeber that this is not a rule but a suggestion). The formula to follow would be:
SELECT what-field(s) FROM what-bject WHERE expression;
We are already familiar with the sections before WHERE. The expression used in a condition is a Boolean expression. This means that the expression written using the formula:
column-name operator value
The column-name must be an existing column of a table or query. It is followed by an appropriate operator. The value is the value that will control the condition.
To make the statement easier to read, you should include it in parentheses after WHERE.
Primary Filtering Operations
The easiest way to create a condition in data analysis is to find an exact value. This is done using the = operator. The formula to follow is:
WHERE field-name = value
If the field is:
Practical Learning: Matching a Value for Data Analysis
Negating a Condition
Sometimes you want the opposite or reverse of an expression. To assist you with this, the SQL provides the NOT operator. There are many ways to use this operator. One way is to precede the operator with the expression.
Practical Learning: Negating a Condition
Hiding a Column
If you apply a condition to a SELECT statement, it is obvious that all values of the conditional field would be the same. In some cases, there is no need to show such a field in the result.
To visually hide a column from a query, in the Criteria pane, set the condition expression in its corresponding Criteria box. Then click the check box of the Show row to remove it.
To hide a field in SQL, omit that column in the SELECT statement but involve it in the WHERE condition. Here is an example:
Practical Learning: Not Showing a Column
A comparison is a Boolean operation that produces a true or a false result, depending on the values on which the comparison is performed. A comparison is performed between two values of the same type; for example, you can compare two numbers, two characters, the names of two cities, the value of one column with a constant, or the values of two columns.
Equality Operator =
To compare two values for equality, use the = operator. The formula to follow is:
value1 = value2
The equality operation is used to find out whether two values are the same. If both values are the same, the comparison produces a True result. If they are different, the comparison renders False.
The equality operation can be illustrated as follows:
You can perform the comparison for equality on columns that use all of the data types we have seen so far but there are some details you should keep in mind:
As mentioned already, to find the opposite of an equality operation, you can precede its expression with the NOT operator.
Practical Learning: Comparing For Equality
Not Equal <>
As opposed to equality, to find out if two values are not equal, use the <> operator. The formula to follow is:
value1 <> value2
The <> operator can be illustrated as follows:
The comparison for inequality follows the same concerns as equality:
The Not Equal operator <> is the opposite to the equality operator =. Of course, you can get the result of a Not Equal operation by preceding an equal expression with the NOT operator.
Practical Learning: Comparing For Inequality
Less Than <
To find out whether one value is lower than another, use the < operator. The formula to apply is:
value1 < value2
The value held by Value1 is compared to that of Value2. The operation can be illustrated as follows:
Practical Learning: Comparing For Lower Value
Less Than Or Equal To <=
The Equality and the Less Than operators can be combined to compare two values. This allows you to know if two values are the same or the first is lower than the second. The operator used is <=. The formula to follow is:
value1 <= value2
If both value1 and value2 are the same, the result is true or positive. If the left operand holds a value lower than the second operand, the result is still true. If the left value is strictly higher than the other Value, the comparison produces a False result.
A <= operation can be illustrated as follows:
Practical Learning: Testing Lower or Equal Value
Greater Than >
To find out if one value is strictly greater than another, you can use the > operator. The formula to apply is:
Value1 > Value2
The > operator can be illustrated as follows:
The > operator is the opposite to <=.
Practical Learning: Comparing For Greater Value
Greater Than or Equal To >=
The greater than and the equality operators can be combined to produce an operator as follows: >=. This is the "greater than or equal to" operator. Its formula is:
Value1 >= Value2
The operation can be illustrated as follows:
The >= operator is the opposite to <.
Practical Learning: Comparing For Greater or Equal Value