[AccessD] A2K: Criterias in CrossTabs

Hi Stuart
many thanks for the prompt reply

Does this also work with dates?
EG
StoredVariable  >=Forms!frmReports!txtStartDate and <= Forms!frmReportstxtEndDAte


Many thanks

Darren

----- Original Message ----- 
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: "Access Developers discussion and problemsolving" <accessd at databaseadvisors.com>
Sent: Friday, August 06, 2004 2:42 PM
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