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