Crosstab Query based on input crieria

[swenger]

I created a Crosstab Query which is based on another simple query. The simple query has a criteria which is a text box on a form.

When I run the Crosstab I get an error that the Criteria is not a valid field. However, if I run the simple query alone it works. Also if I insert hard coded value in the simple query, the crosstab works.

Any ideas how to get this to work how I want it to.



Thanks

[CJ_London]

you probably need to declare the textbox as a parameter (which specifies the data type)

on the ribbon in query design view, click on the parameters option, enter the name of the form field as you have for the criteria and specify the data type (text, long, whatever). In sql view you would see something like

PARAMETERS [Forms]![myForm]![myControl] Long;
SELECT....

note the semi colon at the end of the parameter line

[swenger]

I tried that. The error message I get is "The Microsoft Access database engine does not recognize '[Forms]![Report]![SP]' as a valid field name or expression

My SQL first line is as follows

PARAMETERS Period Text ( 255 );

[swenger]

When I run the Simple query alone now instead of running it first gives me a prompt "Enter Parameter Value" with "Period" which only happened after I entered the Parameters as you mentioned.

[CJ_London]

Report is a reserved word so calling your form that may be causing issues.

Also, the form does need to be open with a textbox called SP

[swenger]

The form is actually called FReport. I used that for simplicity. The form is open and has SP

[CJ_London]

well all I can say is it works in 2010 - perhaps provide your full sql

[swenger]

It is a rather large Database, so I created a new file and exported the neccessary data to replicate my issue which I have uploaded. You will see in the QsumbyIssues Query the last column if I add Period:<Formula> and then add this to the Paramater, when I run the query it prompts me for the Period. If I do not add the Parameter the query runs but the Crosstab Query gives an error.

Thanks,Test.accdb

[CJ_London]

in the QsumbyIssues Query I added the parameters as I suggested and it worked fine

PARAMETERS [Forms]![FReports]![SP] Text ( 255 ), [Forms]![FReports]![EP] Text ( 255 );
SELECT QTickets.[Issue Type....

[swenger]

Thank you. I thought you meant to type the field name in the parameter. Didn't realize to put the criteria field from the form in the parameter