Crosstab query with Parameters in Form

[alfredo245]

Hi Experts,

I just a built a crosstab query by month and business line. I wanted to set a parameter under Business Line. I was having problems with that until I read somewhere that to use crosstab queries with parameters you need to define the parameter first on the query menu. I did that and now I get the pop up dialog box where I can enter my criteria.

Now, I would like to link my parameter to a form. I have already tried using the following

1) In the crosstab query, set to "Where" and in the criteria entered: Forms![Test]![cmb_Business_Line_1]
2) In the Query Parameters, I have entered: Forms![Test]![cmb_Business_Line_1] and set the data type to text.

None of them are working. Is there a way I can link the form a crosstab query or is this not possible in Access 2010?

Thanks!

 

[pbaldy]

Is the form open? Presuming so, double check the spelling of the form and control names. The parameter prompt is Access telling you it can't find something.

 

[alfredo245]

Hi Pbaldy,

Yes. I have double checked. I actually copy it from the builder. I went to builder - forms, selected the object and the whole name was Forms![Test]![cmb_Business_Line_1]

Is it possible to set a parameter on a form for crosstab queries?

 

[pbaldy]

Sure, and the usual problem is not putting it in Parameters, which you've done. The form is open when the query runs? It needs to be, the query won't open it. Can you post the db here?

 

[alfredo245]

Yes. I am not sure how to send you the Db though. How do I post it? Sorry I'm new at this form.

it says invalid post when i try to attach it...

 

[pbaldy]

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[alfredo245]

here it is. Thanks

 

[pbaldy]

You didn't put the form reference in the actual criteria, it still has:

[Enter Business Line]

Which is also in Parameters. Both should have the form reference, and then it works without prompt.

 

[alfredo245]

I had tried that before. But I saved the file when I had made changes. I have made your suggested changes to the database and it is still not working. I get a promt error message.

I have attached a couple of images.

Thanks again

 

[pbaldy]

Get the query into SQL view, delete everything and paste this in:

PARAMETERS Forms![Test]![cmb_Business_Line_1] Text ( 255 );
TRANSFORM Sum(tbl_Main.Actual) AS SumOfActual
SELECT tbl_Main.Business_Line, tbl_Main.Region, tbl_Main.Country, tbl_Main.Account, tbl_Main.FYear
FROM tbl_Main
WHERE (((tbl_Main.Business_Line)=[Forms]![Test]![cmb_Business_Line_1]) AND ((tbl_Main.Account)="Revenue") AND ((tbl_Main.FYear)=2015))
GROUP BY tbl_Main.Business_Line, tbl_Main.Region, tbl_Main.Country, tbl_Main.Account, tbl_Main.FYear
PIVOT tbl_Main.Month_1;

 

[alfredo245]

thank you so much! it does work now. Do you have any idea why it wasn't working? Just want to know in case I want to do something similar in the future.

thanks again!

 

[pbaldy]

Happy to help! To be honest, I was too lazy to download and unzip 3 separate files so I never looked at those.