Microsoft Access Filter and Wildcards - Lesson 43: Filtering a Range of Records

	Filtering a Range of Records

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.

Visually Filtering BETWEEN a Range of Records

To visually 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, 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

As mentioned in the above formula, the BETWEEN keyword is preceded by a name of a field from a table or query. The AND keyword is surrounded by two likely values of that column. The expression that performs the operation is written as:

BETWEEN value1 AND value2

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). Here is an example:
If the values are letters, the first and the last would be compared using their alphabetic positions (Microsoft Access refers to the 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

Filtering BETWEEN a Range in SQL

In the SQL, the formula to apply a BETWEEN ... AND operation is:

WHERE field-name BETWEEN value1 AND value2

Here is an example:

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

Filtering BETWEEN a Rangeof Records

A Single Value BETWEEN a Range

You can use the same value on both sides of the AND operator in a BETWEEN expression.

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 a SELECT statement and use the resulting values in the AND operation. Here is an example:

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. Here is an example:

Filtering BETWEEN a Rangeof Records

Logically Combining Ranges of Selections

You can combine a BETWEEN clause with other logical SQL expressions. Here is an example that combines it with an AND clause:

SELECT PropertyNumber,
       City,
       State,
       PropertyType,
       Bedrooms AS Beds,
       Bathrooms AS Baths,
       YearBuilt,
       MarketValue
FROM   Properties
WHERE  (Bedrooms IN (3,4,5)) AND (MarketValue BETWEEN 350000 And 750000);

Using Expressions in a Logical Conjunction

Practical Learning: Applying a Conjunction to a Record Set

On the Ribbon, click File and click Open
In the list of files, click Kolo Bank1 from Lesson 40
In the Navigation Pane, right-click Account Transactions and click Design View
On the form, right-click the View by Range of Dates button and click Build Event...
In the Choose Builder dialog box, click Code Builder and click OK

Implement the event as follows:

Private Sub cmdViewByDate_Click()
On Error GoTo cmdViewByDate_Click_Error

    If IsNull(txtAccountNumber) Then
        MsgBox "You must provide a valid account number.", _
               vbOKOnly Or vbInformation, "Kolo Bank"
        Exit Sub
    End If
    
    Forms![Account Transactions].sfAccountsTransactions.Form.RecordSource = _
            "SELECT TransactionNumber, " & _
            "       LocationCode, " & _
            "       TransactionDate, " & _
            "       TransactionTime, " & _
            "       TransactionType, " & _
            "       CurrencyType, " & _
            "       DepositAmount, " & _
            "       WithdrawalAmount, " & _
            "       ChargeAmount, " & _
            "       ChargeReason, " & _
            "       Balance " & _
            "FROM Transactions " & _
            "WHERE (AccountNumber = '" & txtAccountNumber & "') AND (TransactionDate BETWEEN #" & txtStartDate & "# AND #" & txtEndDate & "#);"
    
    Forms![Account Transactions].sfAccountsHistories.Form.RecordSource = _
            "SELECT AccountsHistories.AccountHistoryID, " & _
            "       AccountsHistories.AccountNumber, " & _
            "       AccountsHistories.AccountStatus, " & _
            "       AccountsHistories.DateChanged, " & _
            "       AccountsHistories.ShortNote " & _
            "FROM AccountsHistories " & _
            "WHERE (AccountNumber = '" & txtAccountNumber & "') AND (TransactionDate BETWEEN #" & txtStartDate & "# AND #" & txtEndDate & "#);"
  
    Exit Sub
    
cmdViewByDate_Click_Error:
    MsgBox "The account summary could not be displayed because of an error." & vbCrLf & _
           "Error #:     " & Err.Number & vbCrLf & _
           "Description: " & Err.Description, _
           vbOKOnly Or vbInformation, "Kolo Bank"
    Resume Next
End Sub

In the Object combo box, select cmdClose

Implement the event as follows:

Private Sub cmdClose_Click()
    DoCmd.Close
End Sub

Close Microsoft Visual Basic and return to Microsoft Access
Close the form
When asked whether you want to save, click Yes

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. Here is an example:

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

Filtering IN a Group of Records

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. Here is an example:

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

Filtering IN a Group of Records

Logically IN Selections

You can combine an IN clause with other logical SQL expressions. Here is an example:

SELECT PropertyNumber,
       City,
       State,
       PropertyType,
       Bedrooms AS Beds,
       Bathrooms AS Baths,
       YearBuilt,
       MarketValue
FROM   Properties
WHERE  (Bedrooms IN (3,4,5)) AND (MarketValue BETWEEN 350000 And 750000);

Using Expressions in a Logical Conjunction

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.

Here is an example of visually applying the nullity condition:

Setting a Condition on a Query to Isolate Records

Here is an example of testing the nullity in SQL

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

Setting Criteria on a Query

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

Here is an example:

Checking for Not Null

Checking the Negative Nullity of a Field

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.

Here is an example:

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

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

Here is an example:

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;

Here is an example:

Switching a Value for Data Analysis

Special Windows for Data Analysis

Filtering By Value

In a typical table, each column has one or more values. As mentioned in our introduction to sorting, a column may have the same value(s) occurring over and over again while another column may have a different value for each record. To assist you in selecting a value that repeats in a field, Microsoft Access provides a convenient window with the list of values where each is unique. To display that list:

Click a column header or any cell under a column for a table or query, or click a control or its accompanying label on a form. Then, in the Sort & Filter section of the Ribbon, click the Filter button
On a table or query, click the down-pointing button on the right side of the caption of the column

In both cases, a window would display, containing the values of the column on which the action was performed. Each value has a check box:

Filtering Records

In the window that comes up, besides the values of the column, there are the (Select All) and the (Blanks) items (the values of the columns in the checked list box are arranged in alphabetical order; Select All and Blanks are in parentheses because they are not considered for the alphabetical arrangement). To dismiss the window, press Esc or click Cancel.

When the window comes up, to select only the one value whose record(s) must be displayed, clear the check box of (Select All):

Filtering Records - Filtering By Value

Then click the check box on the one item you want. Here is an example:

Filtering Records

Then click OK. This would display only the records that share the value that had the check box:

Filtering

In the same way, you can show only empty fields by selecting the (Blanks) option.

To remove the filter, we saw that you could click the Toggle Filter button on the Ribbon. An alternative is to click the Filtered button in the bottom side of a table, a query, or a form. As an alternative, display the window again, click the (Select All) option and click OK.

Practical Learning: Filtering for a Value

Filtering By Form

Besides the techniques and windows we have used so far for data analysis, Microsoft Access provides another feature referred to as filtering by form. To start it, open a table or query in Datasheet View, or a form in Form View or in Layout View. On the Ribbon, click Home. In the Sort & Filter section of the Ribbon, click Advanced and click Filter By Form. The table, query, or form would become empty and all records would get hidden:

Filter by Form

To select a value, click the cell under the column header. A combo box would come up. Click the arrow of that combo box to display its values:

Filter by Form

The combo box includes each distinct value of the column. If some records are empty, there would be a first empty blank field in the list. To filter the records, select the blank or the desired value from the list. As an alternative, the combo box is an editable text box. This means that, instead of selecting a value from the list, you can type a Boolean expression.

After making a selection or typing the expression, to apply the filter:

Right-click the column header on the table or query, or the control (or its accompanying label) on the form, and click Apply Filter/Sort
In the Sort & Filter section of the Ribbon, click the Toggle Filter button
In the Sort & Filter section of the Ribbon, click Advanced and click Apply Filter/Sort

This action would cause the table, query, or form to display the results.

After filtering, the value (criterion) is stored in memory and you can filter again as many times as you want. Otherwise, you can remove the filter. To do this:

Right-click a cell of the column on which the filtering was performed on a table or query, or right-click the control (or its accompanying label) on the form, and click Clear Filter From ... (this is followed by the name of the field or control)
In the Sort & Filter section of the Ribbon, click the Toggle Filter button
Click any cell in the table or query, or click any control on the form. In the Sort & Filter section of the Ribbon, click Advanced and click Clear All Filters

The Object Filter Window

To assist you in setting a condition for data analysis, Microsoft Access provides a window that resembles the Design View of a query. The window allows you to select one or more fields and set a (some) condition(s) on it/them. The name of that window is a combination of the name of the object from which you opened it, the word Filter, and an incremental number (1, 2, 3, and so on).

To open the Object Filter window, display the table, query, or form in Datasheet View, the form in Form View or in Layout View, or the report in Report View or in Layout View. On the Ribbon, click Home. In the Sort & Filter section of the Ribbon, click Advanced and click Advanced Filter/Sort... A window like the Design View of a query would display.

To use the Object Filter, select (only) the column(s) on which you want to apply a condition. After selecting a column, in the bottom section of the window, in the Criteria box corresponding to the column, type a Boolean expression. After selecting a column and setting a condition, to apply it:

Right-click the window and click Apply Filter/Sort
In the Sort & Filter section of the Ribbon, click Advanced, and click Apply Filter/Sort

To remove the previous criterion, right-click the window and click Clear Grid. After using the grid, you can close it and keep the table, query, or form open.

The Query Builder

Most forms and reports use a table or an existing query as their Record Source. As an alternative, you can specify a SQL statement as the Record Source of a form or report. If you know the statement you want to use, you can directly type it in the Record Source field of the Property Sheet of a form or report in the Design View. As an alternative, you can click the ellipsis button of the Record Source. This would open a special window that resembles the Design View of a query. It is called the Query Builder.

When the Query Builder displays, the Ribbon has a Design tab:

The Design Tab of the Ribbon for the Quiry Buildder

As seen when designing a query, after selecting some fields and optionally setting a condition, if you want to preview the result, click either the View button or the Run button. When you have finished building the query, to return to the form or report, click the Close button on the Ribbon. You may receive a message. Read it and click Yes.

After creating the form or report, if you delete it (the form or the report), the SQL statement would be lost also.


Previous	Copyright © 2000-2022, FunctionX, Inc.	Next