Filtering BETWEEN a Range of Records

Introduction

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:

• If the values are numeric, the first and the last would be compared using the regular algebraic logic that you studied in elementary and secondary schools (such as 4 is between 2 and 5, or 1.02 is between 1.01 and 1.03)
• If the values are letters, the first and the last would be compared using their alphabetic positions (Microsoft Access refers to the alphabetic rules of the language specified in the Control Panel of the computer)
• If the values are text, the characters of each value would be considered by the same positions
• If the values are date or time-based, Microsoft Access would consider the chronological orders

To apply a BETWEEN filter to a table, a query, a form, or a report:

• Display the table or the query in the Design View. In the Property Sheet and in the Filter text box, type the expression. Remember that if you want the filter to apply when you will display the object in the Datasheet View, make sure you set the Filter On Load property to Yes
• Display the form or the report in the Design View. In the Property Sheet, click the Data or the All tab. In the Filter field, type the expression
• Display a query in the Design View and select the field on which the filter will apply. In the bottom side of the window, click the text box that corresponds to the field

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

1. Start Microsoft Access and open the StatesStatistics3 database from the previous lesson
2. On the Ribbon, click Create and click Query Design
3. In the Show Table, click States, click Add, and click Close
4. In the top list, double-click StateName, Abbreviation, AreaSqrMiles, AdmissionUnionDate, and Region
5. In the bottom side of the window, click the Criteria box for the AreaSqrMiles column and type: BETWEEN 50000 AND 100000

   

6. Preview the results in the Datasheet View
7. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
   Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
   Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
   Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)

   

8. Switch to the Design View
9. In the bottom side of the window, delete Between 50000 And 100000
10. Click the Criteria box for the StateName column and type: BETWEEN "i" AND "n"
11. Preview the results in the Datasheet View

    

12. Display the SQL View of the window
13. Change the statement as follows:

    SELECT StateName,
           Abbreviation,
           AreaSqrMiles,
           AdmissionUnionDate,
           Capital
    FROM States
    WHERE AdmissionUnionDate BETWEEN #01/01/1880# AND #12/12/1920#;

14. Preview the result in the Datasheet View

    

15. Close the query without saving it
16. From the resources that accompany these lessons, open the Cruise3 database
17. On the Ribbon, click Create and click Query Design
18. In the Show Table dialog box, click Employees
19. Click Add and click Close
20. In the list, double-click EmployeeNumber, FirstName, LastName, and Nationality
21. Preview the query in the Datasheet View
22. If necessary, on the Ribbon, click Home.
    Change the following characteristics:
    Font Name: Euphemia (if you don't have that font, select Tahoma or Arial)
    Font Color: Gray, Accent 3, Lighter 80% (Theme Colors: 7th Column, 2nd Row)
    Background Color: Blue, Accent 1, Darker 50%  (Theme Colors: 5th Column, 6th Row)
    Alternate Row Color: Blue, Accent 1 (Theme Colors: 4th column, 1st row)

    

23. Return to the Design View

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

1. In the bottom side, click Nationality and press Tab
2. Type [LastName] & ' ' & [FirstName]
3. Click the Criteria box below it and type: Between "Johanson Charles" And "Roberts James"
4. Remove the check of the Show row below that column:

   

5. Preview the result in the Datasheet View

   

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

1. Open the StatesStatistics3 database used earlier
2. On the Ribbon, click Create and click Query Design
3. In the Show Table, click States, click Add, and click Close
4. In the top list, double-click StateName, Abbreviation, AreaSqrMiles, AdmissionUnionDate, and Capital
5. Press Tab and type Right([StateName],2) (this expression asks to select the last 2 letters of the name of a state)
6. To get a list of states whose names end with ad to da with the combination in between, in the corresponding Criteria box, type: Between 'ad' And 'da'
7. Uncheck the Show text box

   

8. Preview the results in the Datasheet View
9. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Bookman Old Style (if you don't have that font, select Times New Roman)
   Font Color: Gold, Accent 4, Lighter 80% (Theme Colors: 8th column, 2nd row)
   Background Color: Blue, Accent 1, Darker 50% (Theme Colors: 5th column, 6th row)
   Alternate Row Color: Blue, Accent 1 (Theme Colors: 5th column, 1st row)

   

10. Close the query without saving it

Filtering IN a Group of Records

Introduction

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

1. Open the Altair Realtors2 database
2. On the Ribbon, click Create and click Query Design
3. In the Show Table dialog box, make sure Properties is selected.
   Click Add and click Close
4. In the list, double-click PropertyNumber, State, City, PropertyType, Condition and MarketValue
5. In the bottom side, click the Criteria box for City and type: IN("Silver Spring", "Gaithersburg", "Rockville", "Germantown")
6. Preview the query in the Datasheet View
7. If necessary, on the Ribbon, click Home.
   Change the following characteristics:
   Font Name: Calisto MT (if you don't have that font, select Times New Roman)
   Background Color: Green, Accent 6, Lighter 40% (Theme Colors: 10th column, 4th row)
   Alternate Row Color: Green, Accent 6, Lighter 80% (Theme Colors: 10th column, 2nd row)

   

8. Display the SQL View of the window
9. Change the statement as follows:

   SELECT PropertyNumber, 
          City,
          State,
          PropertyType,
          Condition,
          MarketValue
   FROM Properties
   WHERE Condition IN ("Excellent" ,"Good Shape");

10. Preview the results in the Datasheet View

    

11. Display the SQL View of the query

Sorting Records

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

1. Change the SQL statement as follows:

   SELECT PropertyNumber,
          City,
          State, 
          PropertyType, 
          Condition, 
          MarketValue
   FROM Properties
   WHERE Condition IN ("Excellent", "Good Shape")
   ORDER BY City;

2. Preview the results in the Datasheet View

   

3. Display the SQL View of the query
4. Change the SQL statement as follows:

   SELECT PropertyNumber,
          City,
          State, 
          PropertyType, 
          Condition,
          Bedrooms AS Beds,
          Bathrooms AS Baths,
          MarketValue
   FROM Properties;

5. Display the Design View

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

1. Click the Criteria box that corresponds to the City column and type IS NULL

   

2. To see the results, in the Results section of the Ribbon, click the Run button

   

3. On the Ribbon, click the down-pointing button below the View button and click SQL View
4. Change the code as follows:

   SELECT Properties.PropertyNumber,
          Properties.City,
          Properties.PropertyType,
          Properties.Condition,
          Properties.MarketValue
   FROM   Properties
   WHERE  Properties.Bedrooms IS NULL;

5. Display the results in the Datasheet View

   

6. After viewing the records, open the SQL View of the query
7. Change the SQL statement as follows:

   SELECT Properties.PropertyNumber,
          Properties.City,
          Properties.PropertyType,
          Properties.Condition,
          Properties.Bedrooms AS Beds,
          Properties.Bathrooms AS Baths,
          Properties.MarketValue
   FROM   Properties;
   

8. Switch to the Design View

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:

• If you want to negate an IS NULL expression, precede it with the NOT operator but put IS NULL in parentheses, as in NOT (IS NULL)
• If you want to indicate that you want records that are not null, use the IS NOT NULL expression

Practical Learning: Checking for Not Null

1. In the bottom section of the window, click the Criteria box that corresponds to the Bedrooms and type IS NOT NULL

   

2. To execute, press F5

   

Conditional Functions

Introduction

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.

Choosing Among Values

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.

Practical Learning: Choosing a Value for Data Analysis

1. Open the Ceil Inn3 database from Lesson 31
2. On the Ribbon, click Create and click Query Design
3. In the Show Table, double-click Employees and click Close
4. In the top list, double-click EmployeeNumber, FirstName, and LastName
5. Right-click the title bar of the window and click SQL View
6. Change the SQL statement as follows:

   SELECT Employees.EmployeeNumber,
          Employees.FirstName,
          Employees.LastName,
          Choose(MaritalStatusID, 'Single', 'Married') AS [Marital Status]
   FROM Employees;
   

7. Preview the results in the Datasheet View

   

8. Return to the SQL View

Switching to a Value

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

1. Change the SQL statement as follows:

   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;

2. To see the result, On the Ribbon, click the Run button

   

3. Close Microsoft Access
4. When asked whether you want to save, click No

Previous	Copyright © 2008-2021, FunctionX, Inc.	Next