[AccessD] A2K: Criterias in CrossTabs

Hi Stephen

An alternative I have used, is to specify the form reference as a
parameter as the reason for the trouble seems to be that Access find
it too difficult to figure out what it is dealing with:

  PARAMETERS Forms!frmCustomers!cmbCustomerID Integer;
  SELECT
    ... etc.

/gustav


> 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

>> 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.