Filtering in the Data Source View

From NeoWiki

Revision as of 15:52, 5 November 2007 by Lorinda (Talk | contribs)
Jump to: navigation, search
This article is part of the To Base and Back Again tutorial series.

Contents

Filtering Data in Data Source View


Once you have selected the query or table you want to work with, you can filter the data using either the Auto Filter button or the Standard Filter button.

Using Auto Filter

The Auto Filter allows you to quickly filter data. Our examples here use the Sightings table

  1. Click in the cell of a record containing the data you want to filter for. E.G. click in the Field Trip ID column in one of the records.
  2. Click on the AutoFilter button on the Table Data toolbar. (It looks like a butler's vest and tie with a funnel). The Data Source View adjusts to show only those sightings with the same Field Trip ID number as the record chosen in step 1.
  3. To add a second filtering parameter, click in another field column of a record containing the value you want to filter for. For example, click a Voice cell where the checkbox is checked.
  4. Double-click on the Autofilter button on the Table Data toolbar. The Data Source View adjusts to show those sightings (in the already-filtered field trip) where Voice is TRUE.
    Note that while it is only necessary to click the Autofilter button once when no filters are currently applied, you have to double click the Autofilter button to apply additional filters to the currently applied filters.
  5. To stop filtering and see all the data again, click either on the Remove Sort/Filter button (a red hexagon with a white x) or Apply Filter button (a green circle with a checkmark). Note that the Apply Filter button is a toggle. When it is highlighted, the filter is applied, when it is not, there is no filter applied.

Using the Standard Filter

While the Autofilter takes the value of the selected cell, the Standard Filter lets you specify a value without seeing it, and lets you build more complete Filter criteria. Unless otherwise stated, we will work with the All Sightings query for these examples.

  1. With the All Sightings query showing in the Data Sources View, click on the Standard Filter button (the funnel icon) on the Table Data toolbar. The Standard Filter window opens.
  2. Set the desired parameters in the Standard Filter window and click OK
    Some specific examples:
    • To filter for a particular text value:
      1. Set the Field Name drop down to Location name
      2. Set the Condition drop down to Like
      3. In the Value field, type Erehwon. Note that once you click elsewhere, the the text string you entered will be surrounded by single quotes to mark it as text.
    • To filter for a range of dates:
      1. Set the Field Name drop down in the first line to Date
      2. Set the Condition drop down in the first line to >=
      3. In the Value field, type 1/1/1900. Note that one you hit return or click elsewhere, a # will be added to the beginning and end of the date "string" to mark it as a date.
      4. Set the Field Name drop down in the second line to Date
      5. Set the Condition drop down in the second line to <=
      6. In the Value field of the second line, type 12/31/1900.
      7. Make sure the Operator drop-down of the second line is set to AND
        Note that the Operator drop-down is greyed out until one of the other settings on that line have been given a value.
    • To filter for a TRUE value in a Boolean (Yes/No) field
      1. Set the Field Name drop down to Voice
      2. Set the Condition drop down to =
      3. In the Value field, type TRUE.
        Instead of TRUE, you can also enter 1. In some localizations of NeoOffice, TRUE (or the equivalent term in that language) does not work in filter dialogs. Instead, you must use 1 for TRUE and 0 for FALSE.
    • To filter for a FALSE value in a Boolean (Yes/No)field
      1. Set the Field Name drop down in the first line to Male
      2. Set the Condition drop down in the first line to =
      3. In the Value field, type FALSE.
        Instead of FALSE, you can also enter 0. In some localizations of NeoOffice, FALSE (or the equivalent term in that language) does not work in filter dialogs. Instead, you must use 0 for FALSE.
      4. Set the Field Name drop down of the second line to Male
      5. Set the Condition drop down in the second ine to null
      6. Set the Operator drop-down of the second line to OR
      Remember that it is impossible to tell whether an empty check box is a FALSE or a NULL value. This is why it is important to search for FALSE or NULL values.
    • To filter for a NULL (empty) field
      this is helpful to find data that was not entered. For the example, we will work with the Field_Trips table.
      1. Set the Field Name drop down to Wind
      2. Set the Condition drop down in the second line to null
      If you have entered the test data specified earlier in the tutorial, this filter condition will actually return no results, because there are no records will null values in the Wind field. You may wish to create a new entry, or edit an existing entry to see how this works.
  3. To remove the filter, click on the Apply Filter or the Remove Sort/Filter Button.

Related Wiki Articles

Sorting in the Data Source View

Personal tools