ffil form filtering utility

October 7, 2004: Fix for RFil and FFil in Access 2003.
A number of users reported problems in the value dropdown lists in RFil 5 and FFil 2 in Access 2003. When clicking on the dropdown to show values to choose from, Access would return an error:
"There was an error in the ..FillPicklist function: There is an invalid use of the . (dot) or ! operator or invalid parentheses."
There was some change in Access 2003 that now causes this error to occur.
To manually fix this bug:
Open the frmCustom form in design mode. Locate the bottom row with small hidden red-text boxes. For the right-most control, called "CurrentPage", change its ControlSource property to:
=Chr(MainTabControl.Value + 65)
Access may add extra brackets, and that is ok. Save the change to the form. The value dropdown list will now function normally.
The original property setting of "=MainTabControl.Pages(MainTabControl.Value).[Name]" would return the correct A,B,C, or D for the current selected tab in versions of Access before 2003. The new setting will be more reliable.

Email me if you have any questions (my email address is at the bottom of this page)

Update October 21, 2002: Fix to RFil 5 and FFil 2
There is a new fix for a bug in RFil 5 and FFil 2 (for Access 2000/XP, not 97). When you filter by a numeric field, then change the same row to filter by a text field, you receive an error about invalid formats and cannot run the filter. This fix is a simple form you import into your application, click a button, and it will fix all of the value dropdowns to correct this bug.
Download fix for this bug (Access 2000/XP, not 97)

The latest FFil 2.025 has the fix applied already. There are no other changes in 2.025.

Introduction:

FFil is a free utility for filtering forms in Access 2/97/2000/XP. With this utility added to your database, users can create, save, and reload filters from one menu. To make filtering easy, FFil provides dropdown lists for the field names, criteria expressions, and values. It can also return a Count of the matching records without opening the form. Runtime Access users also have full filtering capabilities with FFil.

It's easy to add FFil to your Access 2 or 97 databases - just import a few objects and choose which forms can be filtered, and its ready to go.

FFil 2.025 for Access 97 - Updated October 26, 2002

Detailed Info - Screen Shots - Download 97/2000/XP zipped (145k) -  Download Italian 97 (2.0) zipped (165k)

This new release adds many features to FFil 1. You can now use full AND/OR logic in creating filters, there are new options for populating the value dropdown lists, and a new report to document all filters. FFil now uses techniques similar to the RFil 5 report filtering utility, and is DAO specific.

Although not specifically designed for Access 2000, you can use this version in .mdb/.mda/.mde files if you have a DAO 3.6 reference in your code. (Click here for instructions on how to manually set this reference.) FFil 2.0 is not designed to work in the new Access 2000 .adp/.ade format. There may be a future release of FFil especially designed for Access 2000.

International FFil 2 Users - Important Bug Fix! (fixed in 2.02):

Description of problem:
If you are using FFil 2.00, and your regional date setting in Windows is dd/mm/yy or dd/mm/yyyy, you need this fix (2.02). Most U.S. users will not need this fix, but it is highly recommended for international users. At this time, the Italian version of FFil is not updated to 2.02.
When your regional date setting is like dd/mm/yyyy, Access VBA will incorrectly interpret dates, switching the day and month. For example, if you filter on 03/12/2000, FFil 2.00 places # symbols around this date and lets VBA interpret it. VBA incorrectly interprets this as March 12, when you really wanted December 3. When you run the filter, the results will not be accurate. FFil could return matches when there are none, or no matches when there actually are matches. Overall, the problem is in how VBA interprets ambiguous dates. If it is given #03/12/2000# it will ignore the regional date setting you have, and interpret this as March 12th. If you send it #31/12/2000#, which is not ambiguous, then it correctly interprets as December 31st.

How 2.02 fixes this:
This bug fix works around this VBA behavior by converting dates to dd-mmm-yyyy (such as 12-Mar-2000) before adding the # symbols and handing it to VBA to interpret. Since this date format is not ambiguous, VBA can correctly interpret #12-Mar-2000# as March 12th.

Manual fixing 2.00:
If you are confident in making small changes to VBA code, follow these steps:
Open the FFil module "z_FFil2_basMain". Find the function called "ff2MakeSQL".
Move down about 48 lines until you find these 3 lines of code:
Case dbDate
strDelim1 = "#"
strDelim2 = "#"
Immediately AFTER this section and BEFORE the "Case Else" which follows it, add these 2 lines of code:
If Not IsNull(TheValue) Then TheValue = Format(TheValue, "dd-mmm-yyyy")
If Not IsNull(TheValueA) Then TheValueA = Format(TheValueA, "dd-mmm-yyyy")

Compile this change by choosing "Debug - Compile Loaded Modules" from the menubar.
Close the module, saving your changes.

Thanks to Carlo Scaroni for the Italian version!

FFil 1.0 for Access 2 and 97

Detailed Info - Screen Shots - Download 97 zipped (78k)  or  Download 2.0 self-extracting (103k) or Download Spanish 97 self-extracting (105k)

FFil 1.0 has a user-friendly menu for creating and saving simple form filters. You can specify up to 4 fields with 'AND' logic. There are dropdown lists for fields, expressions, and values, and an easy setup menu.

Minor Update: The Access 97 version 1.01 is recently updated to be DAO specific. This is important if you might be upgrading to Access 2000 in the future and plan to use FFil 1. You can either download the fixed version, or see a description on how to set and prioritize a reference to DAO in your older version.