rfil report 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)

July 12, 2003: Bug with RFil 5 in Access 2000.
A user reported a compile problem with RFil 5.025 in Access 2000. A fix for this is the following:
In the main module, search for "Reports(strReportName)", or look in two places: rf5SetupReportInfo and rf5SetupGetRS
Add "Application." in front of the word Reports, to make it:
Application.Reports(strReportName)

For an unknown reason, some recent change in Access, Office, MDAC, DAO, or who knows what else MAY have altered how the Reports collection can be referenced.
The current downloadable version of RFil 5.025 DOES NOT have this fix, because I'm not convinced the fix will work for a user with the old setup or service packs. 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 RFil 5.025 already has this fix, which is the only update.

Update Feb 4, 2002: Fix to RFil
If you are an international user of RFil (versions 5.0-5.02), you may have encountered errors in filtering Date, Number, or Currency fields. A revised version of RFil (5.03) will be made available soon with fixes for these problems. Until then, you can make a few code changes to your existing version to make it filter correctly. Please click here for a description of the steps to apply this patch
The problems that are fixed include:
Filtering on Date fields with non-English month abbreviations (not "Jan", "Feb", etc.) This patch will convert your dates into a format acceptable to VBA (DD-MMM-YYYY) with English month abbreviations.
Filtering on Currency or Number fields with , decimal separator and . thousands separator, and a variety of currency symbols. This patch will clean up currency and number values to a VBA standard. It will also correctly clean up () representing negatives, and keeps exponential notation values (E+ and E-).
This patch does not fix problems with Time values or Long DateTime expressions, which have been difficult to cleanup and standardize. A future patch MAY include this.

Introduction
RFil 5.025 for Access 97/2000/XP - English, Italian (5.0) and Spanish (5.0) versions.
RFil 4.01 for Access 97 - English, German, and Spanish versions.
RFil 3.0 for Access 2 and 97 - English and Italian versions.
Caution about using RFil 4.00 with Access 2000 (DAO reference issue)
Minor Fix 4.0 for using RFil 4 automated setup process in Access 2000 (Container number change)

Introduction to RFil Report Filtering

RFil is a free report filtering utility for Access databases. There are versions available for Access 2.0, 97, and they can also be used in most Access 2000 databases.

Unlike the wide variety of tools and techniques for filtering forms, Access has no built-in tool for filtering reports. Developers have been expected to program complex filtering menus on their own, or use limited parameter queries.

RFil is designed to be a powerful and flexible alternative for filtering your reports with little or no programming effort. It"s "user-friendly" menus give you new options for creating, running, and managing report filters.

All versions of RFil are installed by importing a few objects into your database, and then running a setup procedure which is automated in recent versions. There are no complicated install programs or registry changes, and RFil will not alter your reports. All of the source code is available, most of it with comments, and you are free to modify, enhance, or fix it if you wish! :)

RFil 3, 4 and 5 work in a similar way. There are two different types of report filtering. One uses dropdown lists of fields, expressions, and values to create valid SQL Where clauses to filter reports. The other method uses saved queries and are edited in the standard query design grid. For full information on how RFil works, see the Detailed Info pages for any version below.

RFil 5.025 for Access 97/2000/XP - 

Detailed Info -  Screen Shots -  Download 97 zip (182k) -  Download Italian 97 zipped (178k) -  Download Spanish 97 zipped (172K)

RFil Version 5.x adds extensive functionality over version 4. New features include:

This version can be upgraded to Access 2000, or it can be imported directly into an existing Access 2000 database if it has a reference to DAO. (follow the steps listed in the caution below to set a DAO reference, but don't prioritize DAO above ADO.) Note that new Access 2000 databases DO NOT have the DAO reference by default. You must manually add the reference if you want RFil to work in your new Access 2000 database. Finally, RFil will work in Access 2000 .mdb/.mde/.mda files only - current versions do not work for .adp/.ade files.

Minor fix for replication issue:

In RFil 5.02 or older, and FFil 2.02 or older, here is a simple fix for a replication issue. This only affects users who use RFil/FFil within a replicated database. Unintentionally, a key variable was dimensioned as an Integer, when it needs to be dimmed as a Long (Integer). Replicated databases use large auto numbers, and an "Overflow" error occurs when the variable is only an Integer, and the user tries to save a custom filter.

Here's how to fix this bug manually. A future release of RFil/FFil will include this fix.

Open the sole module that comes with RFil/FFil. Near the top, find the line:
Dim rstIsOpen As Integer, lngFilterIDNew As Integer, intFieldType As Integer

And change the middle section to say "lngFilterIDNew As Long", so overall this line is:
Dim rstIsOpen As Integer, lngFilterIDNew As Long, intFieldType As Integer

Compile and save the module, and it will work normally in a replicated database. Please email me if you have any problems or questions.

International RFil 5 Users - Important Bug Fix! (fixed in 5.02):

Description of problem:
If you are using RFil 5.00 or 5.01, and your regional date setting in Windows is dd/mm/yy or dd/mm/yyyy, you need this fix (5.02). Most U.S. users will not need this fix, but it is highly recommended for international users. At this time, the Spanish and Italian versions of RFil are not updated to 5.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, RFil 5.01 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. RFil 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 5.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 5.01 or 5.00:
If you are confident in making small changes to VBA code, follow these steps:
Open the RFil module "z_RFil5_basMain". Find the function called "rf5CMakeSQL".
Move down about 46 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.

Version History: Version 5.02 correctly filters on dd/mm/yyyy date fields.
Version 5.01 fixes a few issues in 5.00: saving custom filter names that contain apostrophes ('), and better recordset techniques that work correctly when the RFil tables are in SQL Server 7.

  Thanks to Carlo Scaroni for the Italian version!

  Thanks to Roman Rueda for the Spanish version!

RFil 4.01 for Access 97

Detailed Info -  Screen Shots -  Download 97 zipped (93k) -  Deutsche (German version 4.00) self-extracting (126k) -  Spanish Version self-extracting (128k)

This older version of RFil was specifically designed for Access 97. This streamlined version has only a few objects to import, including a new, multi-tab menu for the 2 filtering methods. Error-handling is improved, and miscellaneous bugs are fixed. You can also save filters from the Basic menu as queries, and view them in the Advanced menu.

Minor Update: Version 4.01 is now DAO specific, and it corrects a minor problem with the reports container (described further down) . These changes make it work better in Access 2000 mdb files if you plan to upgrade.

If you are thinking about upgrading your current RFil 4.00 or a database containing RFil 4.00 to Access 2000, please see this Caution about DAO references. Also see this Minor Fix to use the RFil 4.00 automated setup process in an Access 2000 database. (This is because Microsoft changed the Container number for reports from 4 to 5 in Access 2000). Other solutions include downloading the fixed version 4.01, or the new 5.01.

Deutsche Übersetzung Hans Tünnessen (German Translation by Hans Tünnessen - Thank you very much Hans!).

Spanish Translation by Ricardo Carrilero (Thanks Ricardo!!)

RFil 3.0 for Access 2 and 97

More Info -  Screen Shots -  Download Access 2 zipped (60k)  Download 97 zipped (85k)  Download Access 2 self-extracting (74k)  Download 97 self-extracting (99k)  Download Access 2 Italian (52k)

RFil Version 3 has most of the features of 4.0, and is available for both Access 2.0 and 97. This version is not recommended for use with Access 2000 databases.

Thanks to Carlo Scaroni for the Italian version!

Caution  about converting RFil 4.00 to Access 2000 -- The "DAO" Reference Problem:

If you don't want to do these fixes, you can re-download the fixed version 4.01 from the links above.

RFil 4.00 for Access 97 can be imported or converted into an Access 2000 database, to use with standard .mdb, .mde, and .mda databases. (Not .adp/.ade files, however.) You need to set a reference to the DAO 3.6 library and prioritize it above ADO for it to compile and function. This is a common issue with upgrading to Access 2000. (More precisely, RFil 4.00 was not DAO specific in variable declarations, so DAO needs to be prioritized above ADO for it to compile.)

The instructions below explain how to set a reference to the necessary DAO library and to prioritize it above ADO in your database.

WARNING: DO NOT DO THIS IN A PRODUCTION DATABASE WITHOUT FULLY TESTING IT FIRST! I TAKE NO RESPONSIBILITY FOR ANY PROBLEMS THAT MIGHT RESULT!! THIS COULD IMPACT OTHER CODE IN YOUR APPLICATION!!!

To do this referencing, you must be comfortable in finding the code in your database. First, open your Access 2000 database, and view the design of any code module or the code behind any form or report. There is also a shortcut for this - Alt F11. If you have no code, then make a new empty module (you can delete it later or close it without saving).

When in the Visual Basic window, choose: 'Tools-References' from the Menubar. You should see checks next to references including ActiveX Data Objects (ADO). Scroll way down to "Microsoft DAO 3.6 Object Library" and check it. To prioritize it above ADO, follow this shortcut: Hit OK to close the References window, then reopen it. DAO 3.6 will be near the top. Use the up and down arrows to move it up above ADO 2.1. Hit OK again to close the References window. Close whatever module you are viewing.

Once this prioritized reference to DAO is ready, import RFil"s objects. They should compile and run normally in your database. Please be sure to thoroughly test your database to make sure the DAO/ADO reference changes haven't caused problems with other code modules.

Minor Fix - Container Number Change in Access 2000:

If you don't want to do this fix, you can re-download the fixed version 4.01 from the links above.

If you plan to use your current copy of RFil 4.00 in an Access 2000 database (.mdb/.mde/.mda formats only), you will need to make one minor change in code for the automated setup process to function correctly. Microsoft changed the Container number for Reports from 4 in Access 97 to 5 in Access 2000. RFil 4.00 is coded with 4, and it fails to see the reports container in Access 2000.

To make this edit, follow these steps:

View the design of form "frmRFil4". View the code behind the form, by selecting View-Code from the menu bar. Next, do Edit-Find from the menu bar. Enter Containers(4) as your search string, and be sure the "Search: Current Module" choice is selected. This will find the problem line of code in the "RF4AAppendReportInfo" procedure. Cancel the Find, and manually edit this piece of code to:

Containers("Reports")

This makes the entire line of code look like:

Set MyContainer = MyDB.Containers("Reports")

To finish up, choose Debug-Compile from the menu bar to compile the code. Close and save the module and/or the form design. This should fix the problem. E-mail me if you have questions or problems.