Filter by form values in a Crosstab Query

[Guest]

Hello

When I try to filter by form values in a crosstab query, I get a message that the JetDatabase engine does not recognize the form field name (written as [Forms]![FRM_SEARCH]![Search_field_txt])

I am trying to filter a crosstab query by a field on a form. In short, I want the User to enter a value on a form and click the search button to run the query. I want the query to pull all rows from a crosstab query where one of the (Row Heading) fields is equal to the value that the user enters. What I have done is created a crosstab query which contains the rows that I want to pull from, and then created an additional query, pulling all columns from the crosstab query, and filtering by the NAME. Below is the SQL code for the crosstab query I created. Then below that is the search query. What happens when I run the search query is that I get a message that the jetdatabase engine does not recognize the search field name. Any ideas

Thanks

Rebecc

TRANSFORM Count(INST_PROGRAM_XREF.INST_ID) AS CountOfINST_I
SELECT QRY_JOIN4.INST_ID, QRY_JOIN4.FFID, QRY_JOIN4.NAME, QRY_JOIN4.[Region], QRY_RPTS_JOIN4.[Dist], QRY_RPTS_JOIN4.[Dist2], QRY_JOIN4.ACTIVE_FLA
FROM QRY_JOIN4 LEFT JOIN INST_PROGRAM_XREF ON QRY_JOIN4.INST_ID = INST_PROGRAM_XREF.INST_I
GROUP BY QRY_JOIN4.INST_ID, QRY_JOIN4.FFID, QRY_JOIN4.NAME, QRY_JOIN4.[Region], QRY_JOIN4.[Dist], QRY_JOIN4.[Dist2], QRY_JOIN4.ACTIVE_FLA
PIVOT RPTS_RP_INST_PROGRAM_XREF.PROGRAM_ID

Search Query

SELECT QRY_RPTS_JOIN4B.
FROM QRY_RPTS_JOIN4
WHERE ((([QRY_RPTS_JOIN4B].[QRY_RPTS_JOIN4A.NAME]) Like "*"+[Forms]![FRM_SEARCH]![txt_Search_NAME]+"*"))

 

[John Spencer (MVP)]

First thing, with a crosstab query you MUST declare your parameters and if any
other queries are used in the crosstab their parameters must also be declared.

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Something like:

PARAMETERS [Forms]![FRM_SEARCH]![Search_field_txt] Text;
TRANSFORM Count(INST_PROGRAM_XREF.INST_ID) AS CountOfINST_ID
SELECT ...
FROM ...
WHERE SomeField = [Forms]![FRM_SEARCH]![Search_field_txt]
GROUP BY ...
PIVOT ...