Criteria Crosstab (1 Viewer)

[Infinite]

Hello!

TRANSFORM Sum(qryItemsSold.Quantity) AS SumOfQuantity
SELECT tblItems.Model
FROM tblItems INNER JOIN qryItemsSold ON tblItems.ModelID = qryItemsSold.ModelID
WHERE (((Format([Date],"yyyy"))=[Forms]![frmreports]![Text22] Or (Format([Date],"yyyy"))=[Forms]![frmreports]![Text24] Or (Format([Date],"yyyy"))=[Forms]![frmreports]![Text26] Or (Format([Date],"yyyy"))=[Forms]![frmreports]![Text28]))
GROUP BY tblItems.Model
PIVOT Format([Date],"yyyy");

That is what I want. In the WHERE that is going to a Report that has Text boxes that get there info from Check boxes. Anyways, what I need is that to work. When I try to Run/View it, I get the error

"The Microsoft Access database engine does not recognize "[Forms]![frmreports]![Text22]" as a valid field name"

I can just out "2012" in the criteria and it works showing only "2012" but otherwise it doesnt. I am not sure if this is even possible (seems like that because I can with a normal query, and by hand) any help would Greatly be appreciated! Thanks!

 

[JHB]

Below is a link which describe your problem and how to avoid it.
https://support.microsoft.com/en-us/kb/209778

 

[Infinite]

Okay, that is what I have, but the problem I have, is in the picture, you can see I have referring to a form, and those Text boxes. But, the query doesn't do anything. Im not sure what im doing wrong.

 

[JHB]

.. But, the query doesn't do anything. Im not sure what im doing wrong.

Does it not run or ...?
Now you've the parameters, but you need to add it to the query criteria.

 

[Infinite]

Well, I just had to past the !form!name!Text in the Criteria again

Thanks man! Helped a TON! I went through lots of forums looking, and decided I would ask the experts Thanks again!

 

[Infinite]

One last thing, the end result, how would I view that in a form? When I try to just click and drag it into the form (in design view) it has its Source Objects set to nothing. And when I set it to the qry, it gives me the error in the attached image. Its also not creating a sub form like every other query does normally. Thanks!



[EDIT] Solved that, just had to google it some more, and then I found out I had to put the names in the heading field in the settings.