[AccessD] Criteria and Variables

On 18 Jul 2004 at 8:48, Martin Caro wrote:

> Hi Folks
> 
> I'm chasing the correct syntax to use declared variables in the
> Criteria window when building queries. I can place the value in the
> criteria window by first using the variable to load a text box in the
> calling form and referencing the text box from the query but I'm
> presuming it's possible to do directly but...... what am I missing? 
> 

AFAIK, it's not possible to use declared variables,even if they are global in 
scope. My standard solution is to use a static function StoredVariable() for 
this purpose 

Put the function below in a module then you can set the criteria of the query 
to =StoredVariable()

You can intialise StoredVariable  in code from anywhere so you  are not 
dependent on having a specific form open when the query is called. 
Just set the variable with  "result = StoredVariable(myValue)" or just 
"StoredVariable myValue" at any convenient point before you open the query. 


Static Function StoredVariable(Optional varInput As Variant) As Variant
    Dim varStore As Variant
    
    'Initialise the variant if the function is called
    'the first time with no Input
    If varStore = Empty Then varStore = Null
    
    'Update the store if the Input is present
    If Not IsMissing(varInput) Then varStore = varInput
    
    'return the stored value
    StoredVariable = varStore
End Function
 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System Support.