Filtering in Forms

From NeoWiki

Earlier, we discussed Filtering in Queries and Tables. Now we will look at how to filter in forms. We will do so working with the All Sightings form we created in the last tutorial.

Contents 1 Accessing Form Based Filters 2 Sample Filters 2.1 Filter by Exact Text 2.2 Filter by "Contained" Text 2.3 Filtering on two fields (AND) 2.4 Filtering on two fields (OR) 2.5 Filtering to find Empty (Null) Fields 2.6 Filtering by Date

Accessing Form Based Filters

1. Open the All Sightings form. (In the main database document, click on the Forms icon in the left-hand column, then double click on All Sightings.)
2. Make sure that the Form Navigation toolbar is visible. If it is not, go to the View menu, choose Toolbars and then Form Navigation.
3. Click on the Form Based Filters button. It is the the button circled in red below:

   
   

   

   
   

   The Form Filter floating window appears, and the form goes blank. In the case of a data sheet/table, like the All Sightings form, only one empty row is shown.

Sample Filters

Filter by Exact Text

1. Click in the cell under the State heading and type BZ
2. Click on the funnel icon in the Form Filter window. After a brief pause, all records where the state is BZ appear.
3. Click on the Remove Filter/Sort button on the toolbar (The red stop sign with an x) to clear the filter.

Filter by "Contained" Text

The previous example works to find a field with an exact text match. But what if you want to find all records where one field includes a particular word as part of a larger phrase or entry? You need to use place holders

1. Click in the cell under the Bird_Name heading
2. Type LIKE '*Blue*'
3. Click the funnel icon to see the results.

'Note: You can also use the ? character to represent exactly one "wildcard" character. (The * wildcard represents 0 or more arbitrary characters.

Filtering on two fields (AND)

1. Click on the Form Based Filters button again
2. Click on the check box under the Male heading to check it
3. Now click on the Filter Navigation icon in the Form Filter window. (This is the navigator icon with a small funnel on it). A Filter Navigator window will open. It looks like this:

   

   Notice that a TRUE condition is represented by a 1 in this window.

4. In the Filter Navigator, click on the words Filter For next to the first funnel
5. Now click on the checkbox under the Female heading. The words Female: 0 will appear right below Male: 1.
6. Click on the checkbox under the Female heading again to set the condition to Female: 1
7. Click the funnel icon in the Form Filter window. After a brief pause, all records where both Male and Female birds of the same species were seen appear.

   Note that if you had left the Female field set at 0 in the navigator, you would have found those records when only males were seen. Unlike queries, where a criteria of Male = TRUE and Female = FALSE would exclude those records where the Female field is recorded as a NULL, form based filters (in my testing) treat a NULL value in a Boolean (Yes/No or TRUE/FALSE) field as a False. So these records are not excluded

8. 'Do Not clear the filter before continuing with the next example.

Filtering on two fields (OR)

1. Click on the Form Based Filters button again
2. If the Filter Navigator window is not open, click on the Filter Navigation button

   Notice the "Or" between the two funnel images. Conditions under any particular funnel function are AND conditions; that is, all of them must be true. But the different funnels represent OR conditions.

3. Right-click or control-click on the line that say Female: 1 and select Delete from the contextual menu
4. Click on the second (empty) funnel in the Filter Navigator Window. The form will clear again.
5. Click on the check box under the Juvenile box
6. You may need to click on the flippy triangle to see the entry under the second funnel.
7. If necessary, click a second time so that the condition under the second funnel reads Juvenile: 1
8. Click on the funnel icon to see the results

Filtering to find Empty (Null) Fields

1. Click on the Form Based Filters button again
2. If the Filter Navigator window is not open, click on the Filter Navigation button
3. Right-click or control-click on the second funnel and choose Delete from the contextual menu.
4. Right-click or control-click on the line that says Male: 1 under the first funnel. Select Is Null from the contextual menu that appears. the line will change to Male: IS EMPTY.
5. Click on the funnel icon to see the results

Filtering by Date

1. Click on the Form Based Filters button again
2. If the Filter Navigator window is not open, click on the Filter Navigation button
3. Right-click or control click on the first filter and choose Delete from the contextual menu.
4. Click in the cell under the Date heading in the main form and type >3/3/1900.

   Neo will reformat the condition to >#03/03/1900# this is the correct formatting for dates

5. Click on the funnel icon to see the results