Query problem using form text box for criteria

[DanWH]

Hi,

I'm using a simple select query (Qry_A) to query data from a crosstab query
(Qry_B) and it works fine when I input the criteria of '0' directly in the
field of Qry_A.

However when I input the name of a combo box (or text box, I've tried both)
both on the subform that is based on Qry_A and the master form, I get the
error "...does not recognize [Forms]![formA]![Fieldname] as a valid field
name or expression." I've also tried just using [Fieldname] in the criteria
but get the same error that it is not a valid field name or expression.

I have other queries with this syntax that works perfectly, but for some
reason this query is not working.

BTW, this is an Access 2007 database.

Please let me know what I need to do.

thanks
Dan

 

[Wayne-I-M]

Forms![MainFormName]![SubFormName].Form![ControlName]

 

[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
[Forms]![formA]![Fieldname]
Select the data type of the parameter in column 2

If you get an error message about square brackets, open the query in design
view and fix the parameter line by removing the doubled up brackets. For some
reason, Access occasionaly adds extra brackets around the parameter declaration.

Parameters [[Forms]![formA]![Fieldname]] Long;
SELECT ....

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Dan]

Thanks John, That is working the way I need it to. In addition to what you
wrote, I had to define the Column Headings in the query properties to get it
displayed on my form.

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
[Forms]![formA]![Fieldname]
Select the data type of the parameter in column 2

If you get an error message about square brackets, open the query in design
view and fix the parameter line by removing the doubled up brackets. For some
reason, Access occasionaly adds extra brackets around the parameter declaration.

Parameters [[Forms]![formA]![Fieldname]] Long;
SELECT ....

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

I'm using a simple select query (Qry_A) to query data from a crosstab query
(Qry_B) and it works fine when I input the criteria of '0' directly in the
field of Qry_A.

However when I input the name of a combo box (or text box, I've tried both)
both on the subform that is based on Qry_A and the master form, I get the
error "...does not recognize [Forms]![formA]![Fieldname] as a valid field
name or expression." I've also tried just using [Fieldname] in the criteria
but get the same error that it is not a valid field name or expression.

I have other queries with this syntax that works perfectly, but for some
reason this query is not working.

BTW, this is an Access 2007 database.

Please let me know what I need to do.

thanks
Dan