[AccessD] Query difference between Access 97 and 2002

And look at mine where I replace Arthur's static variable with a collection
to turn the function into single function with many storage locations.

'
'Fltr takes two parameters - the filter name, and the filter value (which is
optional).
'
'The first syntax can be used to set the filter value:
'
'fltr "MyFltr1", MyFltrValue
'
'The filter lstrName is used as the key into the collection, i.e. when
lvarValue
'is stored, it is stored with a key of lstrName.
'
'The second syntax can be used to retrieve the value of the filter:
'
'fltr("MyFltr1")
'
'The fact that the second parameter is Optional allows us to check whether a
value
'has been passed in.  If no value is passed in, then the assumption is that
the filter
'is expecting to return a value.
'
'Because the filter uses a collection internally to save the values, this
single
'function can store up to 32K different filter values.
'
'Because lvarValue is a variant, the value stored can be pretty much
anything.
'In fact it is necessary to use ctl.VALUE if you want to store an unchanging
value
'from a control, since passing in a pointer to a control will then return
the value
'of the control, which may change over time.
'
Public Function Fltr(lstrName As String, Optional lvarValue As Variant) As
Variant
On Error GoTo Err_Fltr
Static mcolFilter As Collection
Static blnFltrInitialized As Boolean

    If Not blnFltrInitialized Then
        Set mcolFilter = New Collection
        blnFltrInitialized = True
    End If
    If IsMissing(lvarValue) Then
        On Error Resume Next
        Fltr = mcolFilter(lstrName)
        If Err <> 0 Then
            Fltr = Null
        End If
    Else
        On Error Resume Next
        mcolFilter.Remove lstrName
        mcolFilter.Add lvarValue, lstrName
        Fltr = lvarValue
    End If
Exit_Fltr:
Exit Function
Err_Fltr:
        MsgBox Err.Description, , "Error in Function basFltrFunctions.Fltr"
        Resume Exit_Fltr
    Resume 0    '.FOR TROUBLESHOOTING
End Function



John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Arthur Fuller
Sent: Saturday, August 13, 2005 10:08 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Query difference between Access 97 and 2002


You could also use a static function to put this problem in a nice little
package and forget about it forever. Check the archives for my previous
rants on static functions. If you can't find one, I'll send you the sample
code off-list.

Arthur