[AccessD] Query Criteria problem

Doug,

I was out of the office during this thread but I would like to offer two
things - neither a suggestion for the original problem, but rather thoughts
about static functions.

1) In order to keep the static function "synced" to the form, simply place
the call to the static function in the OnCurrent event of the form.  This
assumes of course that you are using a bound form and the static function is
being called with the data from a field in a table.  Otherwise place the
call in the after update of the control that you are updating and using as a
parameter to the query.

2) Static functions are nice, but you can quickly end up with hundreds of
them if you make the switch and use them all the time.  For this reason I
use a slightly different static function.  The concept is identical EXCEPT
that the storage is a static COLLECTION inside of the function.  To give
credit where due, it was Arthur that started me using these static
functions, however I found I used them so much that I needed something
easier to use.

Contrary to what Arthur apparently believes I do not use a class for this,
but simply a static function like he does.  There are a couple of
differences however between Arthur's method and mine.  I use a SINGLE
function, which I call fltr() but which you can name whatever you wish.
INSIDE of fltr I declare a STATIC collection.  Collections can store any
data type, and they can store MULTIPLE variables.  I then pass in one or two
parameters to the function.  

Function Fltr(strVarName as string, OPTIONAL variant varValue as variant) as
variant
End function

Notice that VarValue is an OPTIONAL value, i.e. you can pass in a value or
not.  However strVarName is NOT optional, you must ALWAYS pass in the name
of the variable you want to get / set.

Notice also that I do not set a default value for the OPTIONAL varValue so
if I do not pass anything in, it is empty.  Thus I can check inside of the
function to see whether I passed in a value.  This "sets me up" for a pair
of rules.

IF isempty(varValue) THEN
	'I am GETTING the VALUE of an already set variable in this branch
ELSE
	'I am SETTING the value of a variable in this branch
ENDIF

In other words, to SET a value I pass in BOTH the name and the value:

	fltr "CompanyName", "Colby Consulting"

If I pass in just the variable name in the first parameter I am RETRIEVING a
previously stored value:

	?fltr("CompanyName")

(returns "Colby Consulting")

Using this method I can have a SINGLE function called fltr() which can store
one or a hundred or a thousand different variables for use in queries like
yours.

Now, the actual function is as follows:

'
'Fltr takes two parameters, the filter name and the filter value.
'
'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 thousands of 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

    'check to see if the collection is initialized yet and do so if
necessary
    If mcolFilter Is Nothing Then
        Set mcolFilter = New Collection
    End If
    'If no lvarValue passed in then this must be a "read"
    If IsMissing(lvarValue) Then
        On Error Resume Next
        Fltr = mcolFilter(lstrName)
        If Err <> 0 Then
            Fltr = Null
        End If
    Else
        'else this must be a write
        On Error Resume Next
        'since there may be a value in there already, delete it
        mcolFilter.Remove lstrName
        'then save the new value passed in
        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

So there you have it.  A single function that can store as many different
variables as you need.  This makes it extremely easy to use these things.
Instead of "cutting / pasting / renaming" a static function to create a new
instance, just call fltr() and pass in the name of the next variable you
want to track.

I hope this was an understandable explanation of how I use static functions
and why I do it the way that I do.

John W. Colby
Colby Consulting
www.ColbyConsulting.com 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Doug Murphy
Sent: Wednesday, August 22, 2007 12:05 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Query Criteria problem

A.D.


Thank you for the information.  I don't have any reservations with using
static functions in queries and do it when required.  I use form derived
values for query criteria since it is easy to do and up to now it worked.
Since I couldn't seem to understand why this isn't working in this case I
went to the static functions and things are progressing nicely.  I would
still like to understand why using form values in a queries criteria does
not work in this project.  Actually it is just in a couple of queries.  I am
sure there is a reason, I just can't figure it out.  

I will keep your message in my archive for future reference.

Thanks again.

Doug