Updating earlier versions of RFil to 5.03

4-Feb-2002 Steve Nyberg www.mile50.com  stevenyberg@yahoo.com

Why this update?
RFil 5.03 will be released soon. However, you can apply most of its fixes to your own database 
 with the following code changes. 

The fixes include: 
better filtering of international date/number/currency fields.
minor fix for a replication issue.


Do this in a TEST COPY of your database application!

Do each of these updates (A through G) in the main module for RFil ("z_RFil5_basMain"):

(For the older Italian version, please see additional notes before the final step)


A)
Near the top above the "Dim" sections, add this section with two new variables for determining if cleanup should be done on Date and Number fields.


'Change the following to apply numeric and date format cleanup.
'  For US/English users, generally they can be False - no format cleanup is needed.
'  For International users with dd/mm/yyyy, decimal symbol of "," and other currency symbols, try setting these to True.
Const blnCleanDate As Boolean = True  'False will use date/time values "As Is" with no cleanup. True will attempt to convert US/International date formats to a VBA/English standard.
Const blnCleanNumber As Boolean = True  'False will use numeric value "As Is" with no cleanup. True will attempt to cleanup numbers to VBA/English standards.





B)
In the main "Dim" section near the top, alter the existing line to look like this. This fixes the replication bug - the datatype was previously "Integer" but needs to be "Long" to work with replicated databases.
Existing line:
Dim rstIsOpen As Integer, lngFilterIDNew As Integer, intFieldType As Integer
Version 5.03 line:
Dim rstIsOpen As Integer, lngFilterIDNew As Long, intFieldType As Integer





C)
At the very bottom of the code in the module, paste in this new, long section of code. It adds two new functions for cleaning up Dates and Numbers.


Public Function rf5CCleanDate(dDate As Variant) As String
    'purpose is to return an unambiguous date string in a format VBA will handle. English dd-mmm-yyyy
    '   appears to be the only reliable way. Otherwise VBA is confused about 07/06/01 and sometimes
    '   treats that as July 6th, other times as June 7th. The dd-mmm-yyyy seems to be converted correctly.
    
    Dim strTemp As String
    Select Case Month(dDate)
        Case 1
            strTemp = "Jan"
        Case 2
            strTemp = "Feb"
        Case 3
            strTemp = "Mar"
        Case 4
            strTemp = "Apr"
        Case 5
            strTemp = "May"
        Case 6
            strTemp = "Jun"
        Case 7
            strTemp = "Jul"
        Case 8
            strTemp = "Aug"
        Case 9
            strTemp = "Sep"
        Case 10
            strTemp = "Oct"
        Case 11
            strTemp = "Nov"
        Case 12
            strTemp = "Dec"
        Case Else
            strTemp = "Error"
    End Select
    strTemp = Day(dDate) & "-" & strTemp & "-" & Year(dDate)
    rf5CCleanDate = strTemp
End Function


Public Function rf5CCleanNumber(ByVal varValue As Variant) As Variant
'Convert a currency or double international number into a format recognized by VBA without cur symbols
'  or , decimals or . thousands separators. Need to keep Exponent E+ and E- symbols however.

    Dim strDecimalSymbol As String
    Dim varTemp As Variant
    Dim intPos As Integer
    Dim strPos As String
    Dim strValid As String
    Dim blnNegative As Boolean
    Dim blnExponent As Boolean
    Dim intPosExponent As Integer
    
    If Not IsNull(varValue) Then
        
        intPos = InStr(varValue, "E+")   'determine if this is a positive exponential number
        If intPos <> 0 Then
            intPosExponent = intPos
            blnExponent = True
            If InStr(varValue, "(") <> 0 Or InStr(varValue, "-") <> 0 Then
                blnNegative = True
            Else
                blnNegative = False
            End If
        End If
        
        intPos = InStr(varValue, "E-")   'determine if this is a negative exponential number
        If intPos <> 0 Then
            intPosExponent = intPos
            blnExponent = True
            If InStr(varValue, "(") <> 0 Or InStr(Mid(varValue, 1, intPos - 1), "-") <> 0 Then  'don't be fooled by the "-" in "E-" into thinking its a negative number overall!
                blnNegative = True
            Else
                blnNegative = False
            End If
        End If
             
        If blnExponent = False Then
            'determine if the number is negative by looking for negative symbols. Later, add "-" in front to avoid weird errors.
            If InStr(varValue, "(") <> 0 Or InStr(varValue, "-") <> 0 Then
                blnNegative = True
            Else
                blnNegative = False
            End If
        End If
        
        strDecimalSymbol = Mid$(CStr(1 / 2), 2, 1)    'determine the regional setting decimal character - thanks to Reinier Olislagers for this method
        
        strValid = "0123456789" & strDecimalSymbol    'the only valid characters to keep as we clean the number
        
        If blnExponent = True Then
            strValid = strValid & "E+-"  'add exponent characters as valid characters
        End If
        
        'move through each character in original string, building result string only for valid characters
        For intPos = 1 To Len(varValue)
            strPos = Mid$(varValue, intPos, 1)
            If InStr(strValid, strPos) <> 0 Then
                If blnExponent = True Then
                    If strPos = "-" Then
                        If intPos > intPosExponent Then  'only add a "-" if its after the E in an exponent (ie E-).
                            varTemp = varTemp & strPos
                        End If
                    Else
                        varTemp = varTemp & strPos
                    End If
                Else
                    varTemp = varTemp & strPos
                End If
            End If
        Next
        
        If InStr(varTemp, strDecimalSymbol) <> 0 Then
            'replace the international decimal symbol with .
            Mid(varTemp, InStr(varTemp, strDecimalSymbol), 1) = "."
        End If
        
        'most reliable way to handle negatives - add "-" in front.
        If blnNegative = True Then varTemp = "-" & varTemp
        
    Else
        varTemp = varValue
    End If

    rf5CCleanNumber = varTemp
End Function




D)
In the function "rf5CMakeSQL", if you have the following section of code, delete it:

If FieldType = dbCurrency Then
    If Not IsNull(TheValue) Then TheValue = CDbl(TheValue)
    If Not IsNull(TheValueA) Then TheValueA = CDbl(TheValueA)
End If



E)
Also in the function "rf5CMakeSQL", modify this section:
After the "Select Case FieldType" line, add a blank line and paste in the following code:

    Case dbCurrency, dbDouble, dbSingle
        strDelim1 = ""
        strDelim2 = ""
        If blnCleanNumber = True Then
            If Not IsNull(TheValue) Then TheValue = rf5CCleanNumber(TheValue)
            If Not IsNull(TheValueA) Then TheValueA = rf5CCleanNumber(TheValueA)
        End If



F)
Also in the function rf5CMakeSQL:
just a few lines down from the above section, modify the "Case dbDate" section to be:

    Case dbDate
        strDelim1 = "#"
        strDelim2 = "#"
        If blnCleanDate = True Then
            If Not IsNull(TheValue) Then TheValue = rf5CCleanDate(TheValue)
            If Not IsNull(TheValueA) Then TheValueA = rf5CCleanDate(TheValueA)
        End If



F2) - ITALIAN Version only: the following may be required to allow for counts of filters. 
In the "rf5CRun" function, find the section near the bottom after "Case 3". 
Comment out (with ') or delete lines to leave the following in the If-Then section:

        If strWhere <> "" Then
		  'commented out 9 lines here
                MsgBox "Ci sono " & lngCount & " records nel filtro"
            'commented out 1 line here
        Else
		  'commented out 9 lines here
                MsgBox "Ci sono " & lngCount & " records nell'OrigineRecord del report"
            'commented out 1 line here
        End If



G)
Compile the code (Menubar choice "Debug-Compile) and test with a variety of date and number values.



If you have questions or problems, please email me at stevenyberg@yahoo.com. Please include 
your version of Access, language, version of RFil (5.0, 5.01, 5.02) and what type of error you receive.