Problem with using criteria from form in crosstab query (1 Viewer)

I have a crosstab query that has the error: The Microsoft Jet engine database does not recognize [Forms]![frmMasterYear]![frmYear] as a valid field or expression.

I have a query called C/Y companies which displays the year and company fields from one of my tables, with the criteria in the year field being [Forms]![frmMasterYear]![frmYear]. Basically, this shows all companys that have a year field in the database equal to what has been selected in the form. In a seperate query (called GeographicFinanicalData) I have joined the company name in the C/Y Companies query to the table which I am getting my fields from as to only show results for companies who have a record with the year matching what is selected in the form. I then use a crosstab query to get summary data from the GeographicFinancialData query. All of the other queries work as a standalone process, however the crosstab query gives the error mentioned above. The crosstab query will work if I change the criteria in the C/Y companies table to something like (SELECT max([Year]) from [4 Premium Analysis]) - I originally had this criteria but I want to change to allow the user to be able to control what year is used as the criteria.

Any suggestions on how to get this to work would be greatly appreciated.

Don't double post.

RV

I got a page cannot be displayed so I posted again, as I thought the original post did not go through, my apologies.

I figured out the solution: Crosstab queries, unlike select or other queries require parameters to be in a certain format. In order to get my crosstab query to work I had to define the parameters, which simply meant going to the query menu and selecting parameters. For the name enter the parameter that is giving you the problem (the criteria I was pulling from the form in my case) [Forms]![frmMasterYear]![frmYear], and then select the type, for mine it was an integer. The crosstab now works fine. Hope this helps someone else.

It helped me... Exactly what I was looking for.

It helped me too

Yep...taught me a couple of things I never knew. My crosstabs now work fine. but the reports based on them still don't [SOB!]

I'll keep searching before I ask a new question. Some people bite your head off around here!

Thanks adamlaing, you have solved a problem i have had for weeks!!!