Crosstab Query

[M8KWR]

I am trying to create a crosstab query that refers to dataa from a form using [forms]![form name]![field name] etc in the criteria underneath field name.

But i am getting the following error message:

The microsoft jet database engine does not recognize '[forms]![form name]![field name]' as a valid field naem or expression.

If a change the query type to a select query then it works fine and only displays the records that i require.

The only quick fix that i can think of is using a make table to store the data that i wish to create a crosstab query on, and thn i know it should work, but i can not see why a crosstab query does not allow you to pull query on data containing in a form.

any help would be appreciate and many thanks in advance.

 

[ZmrAbdulla]

May be you can try it with two queries..
first one (select query) with criteria and the second crosstab from the first query.

________________________________________________________
Zameer Abdulla
Help to find Missing people
You may be the boss' pet; but you are still an animal

 

[PHV]

You have to declare the parameter in the SQL code (eg if date):
PARAMETERS [forms]![form name]![field name] DateTime;
TRANSFORM ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[M8KWR]

no it does not like that either cause your first query is refering to the form... extremely weird why it will not work, or is causing an error. i think creating a new table from my first query is the way to go

 

[M8KWR]

i have never used parameter in crosstab before, as you can tell, what if it a normal text field as well.

I do have 4 fields within the crosstab that i am trying to query on.

Once you have written the parameters in the top bit of the sql code, how do you them assign these to the fields within the query?!?

this is what i have so far

TRANSFORM Sum(Combined.ONDATE) AS SumOfONDATE
SELECT Combined.USERID, Combined.DBName, Combined.ACTVCODE
FROM Combined
GROUP BY Combined.USERID, Combined.DBName, Combined.ACTVCODE
PIVOT Combined.RESULTCODE;

I am querying on USERID, DBName, ACTVCODE, and the ONDATE fields uses 2 fields for the date range.

Sorry for all the questions, but thanks for your help so far.

 

[ChewDoggie]

Good article at Microsoft Support on crosstab queries:

http://support.microsoft.com/default.aspx?scid=kb;en-us;304348

HTH


ciao for niao!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org