Home » Microsoft Access » Data Manipulation

Sorting and Filtering Boolean Fields

Microsoft Access Data Manipulation tutorials

A Boolean field is one that holds a true or false result and it is created as a Yes/No type. By default, and most of the time, a Boolean field displays a check box that can be checked (or marked) or cleared (or unchecked). By its Boolean interpretation, Microsoft Access considers that a check box that is checked is "selected" and a check box that is not checked in "cleared". You can use this logic to sort the records of a table, a query, or a form.

Related Tutorials

Sorting/Filtering Numbers and Dates
One of the particularities of numbers in data entry is that the user types them as they become available. One of the resulting evidences is that, at one time, the numbers in a column may appear disorganized. In some cases this would be just fine. At some other times, you would want to have the values arranged in incremental or reverse incremental orders. The incrementing of a list is the arrangement that goes from the lowest to the highest, such as 0, 1, 2, 3, 4, and so on. The reverse is the arrangement from the highest to the lowest. Microsoft Access provides all the tools necessary to arrange the list in the order of your choice. TOC: Sorting and Filtering Numeric Fields, Sorting Date and Time-Based Fields, Filtering Date and Time-Based Fields, Sorting and Filtering Boolean Fields.

Sorting and Filtering Text-Based Fields
In reality, queries are not the only objects used to analyze data. The main reason for using a query is that it can save its results to be used when necessary. The analysis done on a table or a form is usually temporary and may be lost when you close the table or the form. Other than that, most data analysis operations you can perform on a query are also available on tables and forms. TOC: Sorting Records on Empty Fields, Filtering Empty Fields, Sorting String-Based Fields, Sorting Records in SQL, Filtering String-Based Fields

Sorting and Filtering Information
To let you quickly manipulate information in a table and display it in a more organized fashion, Access provides two special tools: sorting and filtering. Using these tools, you can work with your information in whatever way makes sense to you. This tutorial will explain how sorting and filtering work, as well as how you use these tools to work with your data.

Exploring Data Analysis
To further enhance your filtering capabilities, you can use Boolean algebra combined with the operators we have used so far. Besides the logical operators we know already, Boolean algebra adds extra operators used to concatenate expression.

Retrieving and Arranging Data Meaningfully
We'll cover three important and related topics this lesson: filtering and sorting records, and basic queries. When you Sort records you order them in meaningful ways. When you filter records you hide those you don't want to see. Queries are like powerful filtering and sorting tools that also perform calculations. We'll discuss some basic queries in this lesson and more advanced queries next time. TOC: Retrieving and Arranging Data Meaningfully; Filtering; Basic Queries; Select Queries.

Sorting and Filtering
Sorting and filtering allow you to view records in a table in a different way either by reordering all of the records in the table or view only those records in a table that meet certain criteria that you specify.

Introduction to Data Analysis
Data analysis provides the user with the ability to examine a database's records and the overall behavior of its objects. Data analysis is performed on tables, queries, and/or forms. There are two main aspects involved with data analysis: what you provide to the users and what the users may decide to do with data at their disposal. An intermediate to advanced user of Microsoft Access will know how to perform some, most, or all of the operations we will review. In some other cases, you may want to create objects such as forms and/or reports that isolate records instead of showing all records. Learn: Techniques of Sorting Records, Record Sorting on Tables and Forms, Record Filtering on Datasheet and Form Views, Records Filtering Using Selections, Filter By Exclusion, Filter By Form, Data Analysis With Operators, Advanced Filter By Selection, Data Analysis and Filtering by Comparison.

Sorting report records at runtime
Access reports do their own sorting based on the sort fields you specify in the Sorting and Grouping dialog of the report. The recordsource Order By clause is ignored. Define how a report should be sorted in a Microsoft Access database, at the time the report is opened. The technique involves programmatically setting the ControlSource of the GroupLevel.

Querying a Database
After creating a table and filling it up with some values, you can explore them. One way you can do this consists of isolating records based on specific conditions. This technique of isolating records is also referred to as filtering. To filter records of a table and display the results to the user, you have various alternatives. Data filtering is performed using the SQL and other means provided by Microsoft Access. To filter data, you can either create a query or write a SQL statement. Topics: Queries Fundamentals, Data Selection, Data Analysis: Sorting Records and Data Filtering

Introduction to Sorting Records
Data analysis consists of examining the values stored in an object of a database. The object can be a table, a form, or a query. Data analysis includes sorting the records, filtering the records, and others.

Database Conditional Values
A value is referred to as Boolean when it is either true or false. In other words, the value only be one or the other, never both, and not any other value. To support Boolean values, Microsoft Access provides a data type named Boolean. Learning Objective: Boolean Values, Logical Constants, Logical Operations, Logical Functions, Value Checking Functions, Logical Comparisons.

Sorting String-Based Fields
Most fields of a database contain strings. This allows you to sort them in alphabetical order. When sorting the strings of a column, Microsoft Access refers to the language of the computer set in the Control Panel. That language defines its alphabetical rules. This also implies that the rules in one language are not necessarily the same in another language. To sort the records of a table or query, you must specify the field that would be used as the reference.

Programming PivotTable/PivotChart views in Access
Forms and datasheets in Access support two new views: PivotTable view and PivotChart view. A PivotTable view uses the Office PivotTable Component and facilitates interactive data analysis. A PivotChart view uses the Office Chart Component and helps you create dynamic, interactive charts. The views support interactions, such as adding, filtering, and sorting data, without you having to write code. However, if you want to build a view at run time based on input from the user, you need to write code. This article presents sample code for performing simple tasks, such as moving and filtering fields, in PivotTable and PivotChart views.

How to Filter Records in a Table
Filtering allows you to retrieve specific information from a table. For example, in your recipe collection, you might run a filter to locate all of your breakfast recipes that contain eggs. Filtering by selection is easiest, but filtering with a form is quicker if you want to use multiple criteria to filter. Input filtering is the most precise. These instructions are for Access 97.

Sorting Date and Time-Based Fields
Dates and times represent valuable items of a database. Although they mostly use numbers, their values represent spatial occurrences and therefore are treated accordingly. As done for strings and numbers, fields with date and time values can be sorted. For date values, the results of sorting produce a chronological or a reverse chronological order. Because a date (or time) is a spatial value, it can be considered as occurring...

IIF statements in MS Access
In a ton of my queries in MS Access, I have used IIF statements, to check, to summarize, to distinguish. I have found myself having to write an awful lot of functions by hand to simulate some flexibility that no long exists (I am on SQL server 2000 by the way). I had figured most things could be solved by a function that evaluates a boolean datatype such as IF(Boolean statement, True Value, False Value), but there doesn't seem to be any boolean datatypes, anyone have a similar issue and how did you get around it??

Access 2003: Sorting and Filtering Tables
Learn how to sort and filter the data within your Access tables. (pdf file, install Acrobat Reader to read this tutorial)

Access 2000 Tutorial
In this tutorial you will be going to learn about ms-access screen layouts, creating tables, database records, table relationships, sorting and filtering, queries, forms, form controls, subforms and reports.

MS-Access 2000 Tutorial
Learn ms-access basics, screen layouts, tables, datasheet records, table relationships, sorting and filtering, queries, forms, forms control, subforms, reports and importing and exporting linking.

Access 2000 - Filtering Data
While sorting information in the datasheet view allows you to apply your own ordering to the display of the records in the table, it can also be useful to filter the table. This allows you to limit the number of records that are visible to a manageable sub-set of the records.

ACCESS 2000 - FILTERING DATA
While sorting information in the datasheet view allows you to apply your own ordering to the display of the records in the table, it can also be useful to filter the table. This allows you to limit the number of records that are visible to a manageable sub-set of the records.

Microsoft Access 2007: Pages: 22
In this pdf format tutorial, you'll read: introduction to new interface, getting started with Microsoft Access 2007, Quick Access Toolbar, Navigation Pane, Create a new Table, Add fields to a table in Datasheet view, Save a tabe, Set the primary key, Set field properties, Set table properties, edit and delete records, Resize rows and columns, Hide columns, Finding data in a table, sorting and filtering. (pdf file, install Acrobat Reader to read this Microsoft Office Access 2007 tutorial).

Limit the number of records in a view or report
This topic discusses the techniques that are used in Access for filtering data to restrict the records that are returned in a query, form, or report - not graphics filters. Learn: Filtering in Access (If you are new to filtering in Access, this section explains the basics.), Examples of filters (lists examples that help you define your own filters and explains how to get help from experts on specific or advanced filtering issues.), Work with filters (This section contains the how-to information for working with filters, including saving and removing filters.), More resources (See this section for keyboard shortcuts, filtering commands, more information on troubleshooting filters, and pointers to resources that can get you started with applying filters programmatically.).

Microsoft Access Data Analysis: Filtering By Form
Besides the techniques we saw in previous lessons, as an alternative to filtering by value, you can filter by form.

Create a grouped or summary report
Information is often easier to understand when it is divided into groups. For example, a report that groups sales by region can highlight trends that otherwise might go unnoticed. In addition, placing totals at the end of each group in your report can replace a lot of manual interaction with a calculator. Microsoft Office Access 2007 makes working with grouped reports easy. You can create a basic grouped report by using the Report Wizard, you can add grouping and sorting to an existing report, or you can revise grouping and sorting options that have already been defined. In this article * Understand grouping, report sections, and controls * Build a new grouped report by using the Report Wizard * Fine-tune your report * Add fields or controls to an existing report * Add or modify grouping and sorting in an existing report * Save, print, or send your report * Remarks

How to Filter Records in a Table
Filtering allows you to retrieve specific information from a table in Microsoft Access 2003. For example, in your recipe collection, you might run a filter to locate all of your breakfast recipes that contain eggs. Filtering by selection is easiest, but filtering with a form is quicker if you want to use multiple criteria to filter. Here are some steps to filter records in a table in Access 2003.

Sorting and Filtering Numbers
Numbers provide support for counting in a database and there are different types of numbers, integers and floating-point. One of the particularities of numbers in data entry is that the user types them as they become available. One of the resulting evidences is that, at one time, the numbers in a column may appear disorganized. In some cases this would be just fine. At some other times, you would want to have the values arranged in incremental or reverse incremental orders.

Sorting Microsoft Access Report Fields on the Fly
There may be times when you wish to sort a report into a certain order by a specific field. Reports do not have the same freely available options that are available in Microsoft Access tables, queries and forms, where by the click of a button you can alter the sort order. The following article describes, and includes an example, of how you can allow your users access to change the sort order across multiple fields of the report whilst in print preview.

Operations on Data Analysis
The techniques we used in the last three lessons to analyze data consisted of identifying a value in a particular field and using that value as a basis for filtering. Microsoft Access provides another technique that allows you to select a value from a column and use that value to isolate one or more records. TOC: Filtering for a Value, Using Operators on Queries, Using WHERE, Using IN, Using LIKE.

Microsoft Access Viewlets
Using the Blank Database Wizard in Access. Change Properties for Table Fields in Access. Using the Selection Filter in Access. Using the Report Wizard in Access. Creating an Input Mask in Access. Deleting and adding a Table Field in Access. Creating a Relationship Between Two Tables in Access. Sorting a Table Using Multiple Columns in Access. Freezing and Unfreezing Columns in Access. Sorting a Table in Access. Creating a Simple Query in Access. Navigating Through Table Data in Access. Hiding and Unhiding Columns in Access. Building and Applying a Sort Filter in Access. Searching For and Replacing Text in an Access Table. Automatically adding Date and Time to Access form

Calculations in Queries
Queries often display the information contained in the cells of tables in the database. They may also combine or manipulate data to create new values by using expressions. Expressions may perform mathematical calculations with formulas, combine data or parts of data from multiple fields, or test situations using Boolean logic.

Managing Data Fields
So far, to get acquainted with database fields, we have used two techniques of creating a table and some techniques of adding fields to a form. One of the techniques we used, the Datasheet View, provides a very simplistic way. The Datasheet View is mainly used to perform data entry or to test some fields. It has so many limitations that you will hardly use it. For example, you cannot control or customize the behavior of fields if you create the table in Datasheet View. The other technique we used, the Table Wizard, allows you to use some already created and customized fields. For one thing, you would not know what exactly those fields are configured to do and whether they can control data entered in them. For another thing, if you do not like the way they behave, you would have to modify the configuration that was already done. This is why you should know how to design your tables. TOC: Table Design and Fields, Queries and Fields.

Microsoft Access Tutorials
Introduction (Menu bars, buttons, toolbars, moving around Access),Designing Databases, Creating Tables, Creating and Using Forms, Working with Tables Working with Table Information (Adding, Editing, Deleting, Sorting and Finding Records), Creating Select Queries (Selecting Tables and Fields), Working with Select Queries (Adding, Deleting, Inserting and Moving Fields, Wildcard characters, Multiple Criteria), Creating and Using Reports, Enhancing Forms and Reports

Data Fields on Forms and Reports
As we saw in previous lessons, fields are created on a table, then they can be added to a query. Based on the way Microsoft Access deals with fields, a field on a table cannot be created as a combination of other fields, as if it were an expression .

Microsoft Office 2007
Saving Access 2007 Documents in Access 2003 Format, Creating a Database, Adding Fields, Setting a Primary Key, Setting an Input Mask, Sorting Data, Creating a Query.

Using Data Types
Learn: Number data types (Natural Numbers, Automatic Numbers, Real Numbers, Currency Numbers), Boolean data types, Document and Object Linking, Objects and Data Fields, Hyperlinks, Date and Time, Dates and Times Formats, Date and Time Masks

Searching 6 fields all optional
Hi, Could you possibly help with this... ? I have a second problem which I would love to get some help on, I've hit a brick wall with it. I have a form which has 6 search fields on it. They are used to search for data in the "applicants" form. They are: First Name Last Name Company Post Code Telephone Number Applicant ref I want the user to be able to search on 1 or more or all fields. I appreciate that if the data in one of those fieds is invalid and does not appear in a record then it will not show the resulting record. Although if they enter 3 fields with correct data to minimise returned records to a more accurate set BUT they leave the other 3 fields blank, then I need the query to ignore the blank fields.

Dynamic Sorting Functionality
Quick and Easy Dynamic Sorting Functionality. (video tutorial)

Microsoft Access Query Operators
Queries are a very important feature of Microsoft Access that can tell you information about the data stored in the Access tables. Queries make it easier to search the data from different fields. Therefore, using queries to achieve the results you are looking for is quite a useful tool. However, when you have large amounts of information, you will need more advanced techniques such as Boolean Operators to make sense of the queries you have inputted.

Sorting and Filtering Boolean Fields

View Tutorial