Introduction to C
Filtering BETWEEN a Range of Records
If you have a set of records falling in a range of numeric, alphabetic, or chronological values, you can specify an interval within which the records should be selected. To support this operation, the SQL provides the BETWEEN and the AND operators. The BETWEEN and the AND operators are combined to get a list of records between this and that values.
The operation is formulated as follows:
WHERE field-name BETWEEN value1 AND value2
The value of the left side of AND represents the lowest (or highest) value in the range while the right value represents the highest (or lowest) value of the range. The values can be numeric, textual, or chronological:
To apply a BETWEEN filter to a table, a query, a form, or a report:
In any of these situations, type the expression as:
BETWEEN value1 AND value2
In the SQL, the formula to apply a BETWEEN ... AND operation is:
WHERE field-name BETWEEN value1 AND value2
Practical Learning: Filtering BETWEEN a Range of Records
SELECT StateName, Abbreviation, AreaSqrMiles, AdmissionUnionDate, Capital FROM States WHERE AdmissionUnionDate BETWEEN #01/01/1880# AND #12/12/1920#;
An Expression to Filter a Range of Records
In a BETWEEN ... AND expression, the items on both sides of the AND operator must be constant values, at least at the time the statement is run. The values can come from expressions. Since the AND values must be constants, if you want to use an expression, you can create it in the SELECT statement and use the resulting values in the AND operation.
Practical Learning: Filtering BETWEEN an Expression
A Function to Filter a Range of Records
The values of a BETWEEN ... AND expression can come from a function. As mentioned for expressions, the important rule is that Microsoft Access must be able to evaluate the values on both sides of the AND operator as constants when the results are requested.
Practical Learning: Filtering BETWEEN a Function
Filtering IN a Group of Records
To isolate records for data analysis, you can provide a constant list of values of a certain column and ask Microsoft Access to select only records that include one of the values in that list. To support this operation, the SQL provides the IN operator. The formula to use it is:
IN(value1, value2, value_n)
The IN operator is followed by parentheses. Inside the parentheses, type each of the desired values from the column on which to apply the filter, separated by commas.
To create an IN operation on a table, a query, a form, or a report, use the same steps we reviewed for the BETWEEN ... AND expression.
Practical Learning: Using IN
SELECT PropertyNumber, City, State, PropertyType, Condition, MarketValue FROM Properties WHERE Condition IN ("Excellent" ,"Good Shape");
When filtering records on a range or a group of records, you can sort them. If you are using the Query window, use the Sort box of the text box of the column of your choice. In SQL, remember that the ORDER BY clause is the last section of the SELECT statement.
Practical Learning: Sorting IN and BETWEEN Records
SELECT PropertyNumber, City, State, PropertyType, Condition, MarketValue FROM Properties WHERE Condition IN ("Excellent", "Good Shape") ORDER BY City;
SELECT PropertyNumber, City, State, PropertyType, Condition, Bedrooms AS Beds, Bathrooms AS Baths, MarketValue FROM Properties;
Comparison for Nullity
Checking the Nullity
Depending on how it was created, a field can have an actual value or null. To check whether a field is holding a value or is null, use the following formula for the WHERE clause:
WHERE field-name IS NULL
In this case, only the records that are null or empty in the field-name will be considered in the result.
Practical Learning: Checking for Nullity
SELECT Properties.PropertyNumber, Properties.City, Properties.PropertyType, Properties.Condition, Properties.MarketValue FROM Properties WHERE Properties.Bedrooms IS NULL;
SELECT Properties.PropertyNumber, Properties.City, Properties.PropertyType, Properties.Condition, Properties.Bedrooms AS Beds, Properties.Bathrooms AS Baths, Properties.MarketValue FROM Properties;
Checking the Opposite of Nullity
Instead of considering the null or empty records, the reverse is to get those that are not. To check whether a field doesn't hold a certain value, include the NOT operator in the expression. You have two options:
Practical Learning: Checking for Not Null
As mentioned in Lesson 22, Microsoft Access provides many functions that can be used in conditional statements. Besides the functions we have used so far (IIf, date-based, etc), some functions can be valuable when performing data analysis.
To let you test a condition and select one of the outcomes, Microsoft Access provides a function named Choose(). The Choose() function works like a condition nested in another condition. It tests for a condition and provides different outcomes depending on the result of the test. Its syntax is:
Choose(condition, outcome1, outcome2, outcome_n) As number
The first argument of this function is the condition that should be tested. It should provide a natural number. After this test, the condition may evaluate to 1, 2, 3, or more options. Each outcome is then dealt with. The first outcome will be used if the firt condition produces 1. The second outcome will be used if the condition produces 2, and so on.
SELECT Employees.EmployeeNumber, Employees.FirstName, Employees.LastName, Choose(MaritalStatusID, 'Single', 'Married') AS [Marital Status] FROM Employees;
We know that the IIf() function is used to check a condition and can perform one of its two statements depending on the result of the condition. In some expressions, there will be more than one condition to check. Although you can nest IIf() functions to create a complex expression, Microsoft Access provides another function that can perform this task. The function is called Switch and its syntax is:
Switch(Expression1, What To Do If Expression1 Is True, Expression2, What To Do If Expression2 Is True, Expression_n, What To Do If Expression_n Is True) As Some Value
Unlike IIf(), the Switch() function doesn't take a fixed number of arguments. It takes as many combinations of <Expression -> Statement>s as you need. Each expression is evaluated. If the expression evaluates to true, the statement that follows it executes.
Although you can spend a great deal of time tuning a conditional expression such as one involving a Switch() function, it is still possible that none of the expressions evaluates to true. In this case, you can add a last expression as True and provide a subsequent statement to use. The syntax you would use is:
Switch(Expression1, What To Do If Expression1 Is True, Expression2, What To Do If Expression2 Is True, Expression_n, What To Do If Expression_n Is True, True, What To Do With A False Expression) As Some Value
Practical Learning: Switching a Value for Data Analysis
SELECT EmployeeNumber, FirstName, LastName, Choose(MaritalStatusID, 'Single', 'Married') AS [Marital Status], Switch(FilingStatusID = 1, 'Unknown', FilingStatusID = 2, 'Head of Household', FilingStatusID = 3, 'Married Filing Jointly') AS [Filing Status] FROM Employees;
|Previous||Copyright © 2008-2021, FunctionX, Inc.||Next|