[AccessD] A2K: Criterias in CrossTabs

That's good Stuart, I used to solve this by creating a temp table from the query with the criteria, then feeding this into the XtabQ.   Good to find a better mousetrap.
 
Stephen Bond

-----Original Message-----
From: Stuart McLachlan [mailto:stuart at lexacorp.com.pg]
Sent: Friday, 6 August 2004 4:42 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] A2K: Criterias in CrossTabs



On 6 Aug 2004 at 14:20, Darren DICK wrote: 
> 
> The cross tab is fed by a query not a table - So I went to the underlying 'feeder query'... 
> and put in Forms!frmCustomers!cmbCustomerID into the 'feeder. Then ran the crosstab again... 
> Same result - same error message. 
> But if I run the 'feeder query' by itself using the Forms!frmCustomers!cmbCustomerID in the criteria portion of the grid - all is OK

> 
> Is it the case that Crosstabs can't handle changeable or form bound criterias? 
> 

Yep, this problem bites in a number of situations. 

My standard solution is to use a static function to store the content of 
the form's control and use that in the query. 

Put the following function in a module: 

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 IsEmpty(varStore) 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 

In the query, replace "Forms!frmCustomers!cmbCustomerID " with 
"=StoredVariable()" 

Then put 
"StoredVariable Forms!frmCustomers!cmbCustomerID" 
or "returnvalue = StoredVariable(Forms!frmCustomers!cmbCustomerID)" 
in code somewhere before you call the query. 





-- 
Lexacorp Ltd 
http://www.lexacorp.com.pg 
Information Technology Consultancy, Software Development,System Support. 



-- 
_______________________________________________ 
AccessD mailing list 
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com