[AccessD] Crosstab Querry with parameter on Form (A97)

Hi Arthur,

Yeah! Works like a charm.

Here is what I did in case someone else gets the same error:

I created a new module (I called it bas_StaticFunctions)
Created the two functions:

Static Function StartDate() As Date
  StartDate = CDate(Forms.frm_Param.txt_StartInvoiceDate)
End Function

Static Function EndDate() As Date
  EndDate = CDate(Forms.frm_Param.txt_EndInvoiceDate)
End Function

In the criteria section of the query I put:
Between StartDate() And EndDate()

Note:
The form "frm_Param" as to be opened in order for this to work. Note also
that no validation is made in the functions StartDate() and EndDate() so
validation for dates has to be done at the form level.


Thanks a million!


Steve

-----Message d'origine-----
De : accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]De la part de Arthur Fuller
Envoye : Monday, March 10, 2003 09:01
A : accessd at databaseadvisors.com
Objet : RE: [AccessD] Crosstab Querry with parameter on Form (A97)


IME the best way to get around the limits of crosstab queries is to first
create a query that gets all the data you need, then create a crosstab query
that uses the first query as its source.
Capture the values from your form using static functions, and then call
those in the first query. The crosstab won't even see them.
If you're unfamiliar with static functions, search the message base and you
should find several messages in which I describe their purpose and use.
A.

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com] On Behalf Of Steve Grant
Sent: March 10, 2003 8:40 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Crosstab Querry with parameter on Form (A97)

Access 97

Hi all,

I am running into a problem that eludes me.

I have a query that uses a form to get it's parameters.
This is what's in the criteria section of the query.
Between [forms].[frm_Param]![txt_StartInvoiceDate] And
[forms]![frm_Param].[txt_EndInvoiceDate]

When I run that query, everything works fine, i.e. the correct records are
displayed.

But, when I use that query in a crosstab query, I get the following error
msg:
"The Microsoft Jet Database engine does not recognise
[forms].[frm_Param]![txt_StartInvoiceDate]
as a valid field or expression."

The crosstab query works fine if I use dates instead of a reference to a
field on a form. So if I put:
Between #01/01/2003# And #01/31/2003# in the non crosstab query, the
crosstab query works fine.

I tried putting the criteria directly in the crosstab query and I get the
same error msg.

Anyone as an idea why this is hapening?

Thanks,


Steve